Can An Authorized User Like SA Create A Table Which The Owner Is Any Users Who Use The Application?
Aug 20, 2007
Hi,
autorized user: sa
any user : nuran
temporary table: birtablo
I need a stored procedure will execute by sa and it will create some required temporary tables for each users. For example table name is birtablo. I mean sa will create table for nuran, and when I checked the owner of the table (birtablo) I want to see nuran not dbo.
sa will execute following command:
create table nuran.birtablo (...........)
Is it possible to cerate a table by sa on behalf of any user? If it is, could you please explain?
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
There's something I can't quite figure out about user creating
The application that I'm currently working on is interacting with DB, therefore every time you use application you need to login as user which is fine. The problem is that certain users should be able to create new users and the new user may even have the same level of permissions as the one that's creating it ( like admin creating another admin acount or some like that).
Question is how can I allow users to create these user with giving them as few permissions as possible.
If there's is somewhere a code sample on the net I would appreciate the link.
CREATE ASSEMBLY ClrSql from 'C:ClrSql.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
gives me this . . .
Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'ClrSql' failed because assembly 'ClrSql' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
How do I allow EXTERNAL_ACCESS and UNSAFE permissions? The property appears grayed out, and I am the dbo.
I have a requirement to allow a user to restore a database and then create database users and add them to the db_owner database role. The user must not have sysadmin rights on the server.
The database restore works ok by placing the user in the dbcreator role.
There is a stored procedure to create the database user and alter role membership, I want the user to execute the sp as a different, higher privilege account so as not to give the user underlying permission to create users in the database.
USE [master] GO
/****** Object: StoredProcedure [dbo].[sp_create_db_users] Script Date: 22/07/2014 13:54:46 ******/ SET ANSI_NULLS ON GO
[Code] ....
The user has execute permission on the stored procedure but keeps getting the error:
Msg 916, Level 14, State 1, Line 2
The server principal "Mydomainadmin1" is not able to access the database "Mydatabase" under the current security context.
Mydomainadmin1 has dbowner to Mydatabase and sysadmin rights for server. If the 'execute as' is changed to 'caller' and run by mydomainadmin1 it works so the issue is between the execute sp and the actual running of the procedure.
HiOverview - non-sysadmin access to SQL 2k box using 2k5 tools. Not used 2k in over a year,Any way to set the default owner for objects created by a user? So if user x runs: CREATE TABLE mytable ( mycol BIT ) it is owned by dbo not x (dbo.mytable not x.mytable)? Note I can't (or won't for now anyway) change the DDL above. I also don't believe I can do this with the SSMS tools (schema <> owner). I also can't use sp_adduser...So 2k comliant T-SQL or at a push SMO only please!Ta
i am creating a model in which the control panel i am building for a web application, i can write sql statements in a textbox and the query will be done in my application. But i am have a problem creating a table. this error of "Create table permission not granted in the sql server" pops up whenever i want to execute the create table sql statements from my web page. How can i permit my sql server to allow creation of tables from my web application. Thanks
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database) Help pleaseee
I am working with a vendor application called Cisco Unified Attendant Console - it operates on a Windows server with a SQL express database. The CUPs function of the application needs to reference a "contact" field with only the user portion of the contact's email address - generally, the contact's User ID will match the user portion of their email address, however, for this customer it does not (they use the employee number as the User ID and firstname.lastname as user portion of the email address.
Writing a script to accomplish the following:
The dbo.Contact_Properties table of the ATTCFG database has the following fields that we can work with:
- First_Name - Last_Name - Email - User_Field_2 - Contact_Unique_Ref (appears to be the field that ties all other contact tables together ?)
Is it possible to create a script that could run daily to either, combine the First_Name and Last_Name fields (with a period between) and populate the User_Field_2 field for each user, or populate the User_Field_2 field with everything before the @ symbol in the Email field for each user?
Also, by default the servers that this application is installed on does not have SQL Server Management Studio installed - is it possible to accomplish with PowerShell script triggered from the Windows Scheduler?
How can I get different between DBO owner and another user when I select SP name from SYSOBJECTS? How to compare a SP's create by DBO and another user? Example for, I want to list all SPs of DBO owner to excute my statement (as sp_changeobjowner...), but when I select all SPs in SYSOBJECTS to cursor, if there is any SPname's created by another user (no DBO), I'm getting error. Thanks in advance, J.K
We have SQL2000 and generally each user uses Windows authentication for their Enterprise Manager connections. When developers create tables, I would like them all to show DBO as the owner (DBO.TABLEA), not JOE.TABLEA.
I am developing an distributed VB.NET 1.1 application with a TripleDES capable socket layer for communication with my server app.
I need to secure the distributed app from the users within the organization I am developing it for (a franchise).
I do not wish to store any encryption keys in the source code as these would be obvious to any seasoned hacker through decompilation of my binaries (even with obfuscation). I have decided to use the windows DPAPI (under machine storage mode) to secure manually entered (at installation) encryption layer keys in the registry. The salt values for this DPAPI mode also need to be secured, as a disgruntled franchise owner may be the hacker (and hence would have admin privilege on the machine the software is installed on). Not as far fetched as you think !
This is the beginning of a vicious cycle. How do I secure and where do I store this salt value safely ? With it a hacker with admin privilige can easily decrypt my keys if they know I am using machine mode DPAPI. Can I use ACLs to protect the keys with an account I set up manually on the machine ? If so then I would need to be able to switch account identities in my code (which I haven't researched as yet) and then would need to store the password to that somewhere.
If anyone could offer any insight or direction it would be much appreciated.
User bob has create procedure permission as follows:
grant create procedure to bob
bob would like to create a procedure in schema Accounts.
When he issues create proc Accounts.sp_proc.... it fails with:
Msg 2760, Level 16, State 1, Procedure sp_proc, Line 3 The specified schema name "Accounts" either does not exist or you do not have permission to use it.
What permission do I need to grant bob in order to allow this?
I have a SQL 2005 question for you. In the SQL server Management Studio. When I right click on the database and use the SQL Server Import and Export wizard, I can import databases but cannot export them.
The error says:
The current user is not an owner of the selected database. This might cause some operations with this database to fail.
I use sa to loginto the server Management Studio, does anyone know how to create a database where the user and owner are the smae so I can export?
How do I allow a user (or group of users) permission to create/drop a table?
I have found the 'GRANT CREATE TABLE TO username' command, which will (I assume) allow a user to create a table, but how to I allow a user to 'DROP' the created table as well? 'GRANT DROP TABLE TO username' doesn't work? and I want the users to be able to DROP/DELETE this table (temporary table created just for printing purposes) as well.
I have recently installed SQL 2005 I use the SQL ecpress and management studio. In the SQL server Management Studio, when I right click on the database and use the SQL Server Import and Export wizard, I can import databases from the web hosting I'm workign with to my mycomputer, but cannot export them. When I try to export database tables with the SQL Server Import and Export wizard The error says: The current user is not an owner of the selected database. This might cause some operations with this database to fail.I use sa to log into the management studio aand creat teh database after I login. How do I create a database so that the user and owner match and will allow me to export tables and data?
Usually all the user datatypes in our databases have the owner dbo. One has a few that are owned by a user with dbo rights. I am trying to change them to dbo owner. Sp_changeobjectowner gives 'object does not exist'
Hello, We had a developer that created some user defined data types. He is no longer with our company and we want to change owner of those user defined data types so we will be able to delete his UUID. Is their a way to change the owner of the user defined data types from his ID to dbo. I don't see a way to change them with sp_changeobjectowner
I have a SQL 2005 question for you. In the SQL server Management Studio. When I right click on the database and use the SQL Server Import and Export wizard, I can import databases but cannot export them.
The error says:
The current user is not an owner of the selected database. This might cause some operations with this database to fail.
I use sa to loginto the server Management Studio, does anyone know how to create a database where the user and owner are the smae so I can export?
Hi all hope some one can help - please bear with me new to this Basically I have had to change pc's so I copied and pasted my ASPNET.MDF and LDF from my old pc to new PC including webpages/apps etc.. created. However now all I get is "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path """" Just don't understand what permissions the db should have, does it need to match the SQL Express owner...
I installed my asp.net 2.0 web application and sql express june editon on my windows 2003 server.When the application tries to reach the database i am getting the following error"Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed."I can understand that it has something to do with user right but more than that I am pretty lost. Can anyone translate this error for me and give me some tips on what do to.I checked first the user running the sql express service and saw it was the NETWORK SERVICE user. I changed it to be the local system account but that did not make any difference. The application is running just fine on my local computer(Win XP sp2). This one has SQL express April edition though.
ok suppose i want to create a table with a user specified name...... how should i replace the name with the variable name="table1" Dim condb string ....... Dim cmdDB2 As SqlCommand Dim name as string=("table1") Dim selkta As String = "CREATE TABLE name(no int,quest text,op1 text)" cmdDB2 = New SqlClient.SqlCommand(selkta, conDB) conDB.Open() cmdDB2.ExecuteNonQuery() conDB.Close()thank u
Hi GuysWonder if you could help me.Basically I produce an accounts package that uses a SQL 2000 DB as theRDBMS. I always instruct users to login as 'sa' and the relevantpassword when doing an update to my program, as sometimes I need to dodatabase changes for new stuff.Found that one of my users has not only logged in with their loginname (in this case Edward), but have also made this login a 'db owner'so that when I created 2 new user-defined data types they belong toEdward rather than dbo.This must have happened a long time ago, but now that they want tomove Edward round the roles and/or delete him from a copy of thedatabase that they have, they can't because he's the owner of theseuser-defined types.This brings me to the reason for my post, how can I change the ownerfrom Edward to dbo for these data types? I found an article ontechnet of how to do this, but when it suggests changing myuser-defined type to standard format it doesn't seem to work.Any ideas?RgdsRobbie
Looks like Using Schema in SQL Server 2005 gives enough fucntionality to control persimission on pre-created objects and database. here is my scenario....
I want to have Users that can only create new databases and then have FULL control on that database that they create, like creating table, inserrting, deleting, updating, etc etc.
what will be best steps to have this scene.
(I have created a user and made it a memeber of DBCREATOR, but that user is unable to create any table inside database that is created by that user),
Any help is highly appreciated. I am using SQL 2005
vendor did a full backup for his database and put some folder not being backed up by TSM, he is the owner of the database, and delete his backup later. The backup is not copy only, all the differential and log backups taken are based on his FULL backup. so they cannot be restored.
QUESTION: To prevent this happen in the future, what is the normal practices? or any way to prevent db_owner to do the ad-hoc full backup? I am thinking of using DENY backup database, or write a policy claiming no responsibility if vendor make it happen again.
Hi everybody,I've five instances of SQL Server 2000 with the SAME database with aDIFFERENT owner in each server. I, as the administrator, have a lot ofqueries that I have to execute in some or all servers. The problem isthat I have to connect to all servers with MY user, not each of the dbowners...So I have queries this way:select * from mike.table1 t1 join mike.table2 t2 on...And when I connect to another server I have to change mike for jeremyin all the SQLs...And when I connect to another server I have to change jeremy for ninain all the SQLs...I know that there was an old, v7, deprecated way to change the"schema", something likechange current user to kimberlygoselect * from table1 t1 join table2 t2 on...This way, I'll change ONLY once the connected user. I could even do atthe beginning of the script an IF, to change the connected userdepending on @@SERVERNAME !!!Can someone remember this instruction???Thanks in advance for your help !!!
Hello, i have a problem regarding stored procedures and view server state.
I have an application with a lot of stored procedures, one of them checks data of the connected users. In SQL 2000 i had no problem getting this information, but in SQL server 2005 i do.
my stored procedure looks like this:
ALTER PROCEDURE [dba].[applsp_GetConnectionInfo]
(
@DBName varchar(100)
)
WITH EXECUTE AS OWNER AS
BEGIN
SET NOCOUNT ON
DECLARE @sCollationMaster VARCHAR(128);
DECLARE @sSqlString VARCHAR(900);
-- Determine collation from master database because collation from master and ultimo database may differ
SELECT @sCollationMaster = CAST(databasepropertyex('master', 'Collation') AS VARCHAR);
SET @sSqlString =
'SELECT max(status) AS Status, max(isnull(SCISUSENAME, ''ULTIMOLOGIN'')) AS Login
, MAX(Rtrim(Rtrim(convert(varchar(255), nt_domain)) + nt_username)) AS NTUser
, max(Rtrim(hostname)) AS Host, MAX(Rtrim(program_name)) AS Program
FROM master.dbo.sysprocesses JOIN dba.SCONNECTIONINFO on SCISPID = CAST(spid AS VARCHAR)
AND ( SCISUSENAME = ISNULL(loginame, '''') COLLATE ' + @sCollationMaster + ' OR ISNULL(loginame, '''') = ''ULTIMOLOGIN'')
WHERE ...... AND DB_NAME(dbid) = ''' + @DBName + '''
GROUP BY hostprocess
ORDER BY Login
';
EXEC(@sSqlString);
END
I've granted view server state permissions to my user 'dba' which is the db_owner. When i execute the query in the stored procedure seperatly as dba i get all the info i need, but when i execute the stored procedure i don't see anything.
I seem to have the same problem with sp_who2 Executing it gives me information about everyone but when i put in a stored procedure like this:
Setting up a test AlwaysOn Availability Group for one database.
However, whenever I restore the database to the replica server and join it, it ends up with my user account as the owner of the database.
Obviously I do not want a user account as the database owner, but since it is read-only I cannot modify it directly. If I were able to fail the AG over to the replica, I could change the owner then, but I cannot due to business requirements. this AG is to essentially serve as a replacement to log shipping.
I tried doing the backups and restores using EXECUTE AS login = 'sa', and yet it still shows up as my user account.
