How To Create A User With Sa Privilege
Feb 16, 1999Using SQL 6.5 SP4.
TIA.
Zak
ADVERTISEMENT
SQL 2012 :: Allow Low Privilege User To Execute Stored Procedure To Create DB Users
Jul 22, 2014I 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.
SQL Security :: How To Create A Low Privilege User And Role For Server Session State
May 26, 2015An old website I inherited uses sa to connect to SQL SessionState and had the details in the web.config. This is bad for security.The session state database is of -sstype "t" which is defined as:Temporary. Session state data is stored in the SQL Server tempdb database. Stored procedures for managing session state are installed in the SQL Server ASPState database. Data is not persisted if you restart SQL. This is the default.What kind of WIndows user, SQL Login, role and permissions do I need to create to make Session State secure? (Windows Server 2012 and SQL Server 2012 mixed mode authentication, Webfarm).
View 4 Replies View RelatedSQL 2012 :: How To Grant A New User Same Direct Privilege To Tables As Existing User
Feb 18, 2014I am relatively new to sql developer. There is a new user that just joined our organization. I am trying to grant him the same direct grants privilege to the tables that an existing user has. The existing user has a ton of direct table access privileges and it will take days if I had to do each grant one by one like: grant select,insert,delete,update on 'table name' to 'user id'. Is there a way of copying or inserting an existing user's privilege and granting it to a new user.
View 2 Replies View RelatedCan I Grant All Privilege To User?
Aug 24, 2006it is possible to grant all privilege (CRUD) to specified table to user. But, now, i want to grant all privilege (CRUD) of all tables, views, sp, ... of database to the user. is it possible?regards,
View 5 Replies View RelatedDatabase User Privilege Report
Jul 23, 2005hi, can anyone help me out with this report.I need to produce a report for the database level user permission.like for a database: DB_A, I want to output as such:[SELECT] [INSERT] [UPDATE] [DELETE] [CREATE TABLE] .... otherpublic G Dguest D D D D Ddbo G G G G Guser_A G D D D GG stands for Grant and D stands for Deny.I need not only to look at direct grant/deny but also whether that useris a member of fixed database role and fixed server role. like dbo willhave G on all.I am trying to look into sysprotects table and looking at lines with id= 0 but I just can't get to know what different action and protecttypenumbers means!Many thanks
View 1 Replies View RelatedHow To Assign Database Access Privilege To User?
May 26, 2008Hi friends,
I have created a database DB1 using CREATE DATABASE DB1 command. Then i created login name using CREATE LOGIN login1 WITH PASSWORD = 'password1' command and created user name using CREATE USER user1 FOR LOGIN login1 command. Now i have to assign the user1 to the database DB1.
Any one please tell me how to assign DB1 access privilege to user1?
Thanks in Advance
Sathish kumar D
Domain User Privilege For Connecting SQL Server 2000
Mar 30, 2006Hi,
My server administrator has created a few users as domain user privilege for connecting SQL server from my application. However it faild to connect, and
with Domain Admin privilege, my app is running ok.
I asked admin person to upgrade domain user privilege to connect SQL server,
but he couldn't do this for security reason, and told it should work with domain user.
Is it true for domain user to connect SQL server without any problem?
Do I need to add the user, domain user, into Logins of Security inside SQL server Enterprise Manager?
Or inside users of my database section of SQL server EManager?
Thanks,
dixon
Howto Setup My User In Order To Grant Him The Drop Privilege
Sep 28, 2005Hi. Thru a sproc, I drop & re-create some temp tables.When I call that sproc from the client, though, I cannot drop thetables.I need to allow the user, say "Alex", to drop/create tables (actually,that would be DDL). Which role should "Alex" assume ? How do I do that?I run the following sproc named, say, "CREATE_TABLE" (SNIP):__________________________________________________ __________________Set @StrSQL = 'if exists (select * from dbo.sysobjects where id =object_id(N''[dbo].[' + @TableName + ']'') and OBJECTPROPERTY(id,N''IsUserTable'') = 1) drop table [dbo].[' + @TableName + ']'Exec (@StrSQL)Set @StrSQL = 'CREATE TABLE [dbo].[' + @TableName + '] ([GROUP] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,...........[Stuff] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL) ON [PRIMARY]'Exec (@StrSQL)Set @StrSQL = 'GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].['+ @TableName + '] TO [Alex]'Exec (@StrSQL)__________________________________________________ __________________As you can see, it is dynamic, because I need to repeat it for many@TableName values - that means, further more, that I will be executingthis in the context of the current user, Alex, and therefore I have togive Alex rights to both executing the sproc and to the tables referredto by the sproc, as specified by @TableName.I created a _TEST sproc which contains only the following:_______________________________________________CREATE PROCEDURE _TEST ASDROP TABLE [dbo].[SomeTable]RETURN_______________________________________________When I execute it from the client, thru ADODB, on user Alex, I get"User does not have permission to execute this operation on tableSomeTable"The table has been created thru "CREATE_TABLE", abovePlease help, I have to finish this tomorrow, and I'm under tons ofpressure.Thanks a lot,Alex.
View 1 Replies View RelatedSQL Server 2012 :: Restore Database And Create Users Using Minimal Elevated Privilege
Apr 29, 2015What I want to do is :
- restore a backup of a 3rd party database onto one of our servers
- this has no users that I can use
- there is some ETL processing so we're using Control-M to manage the process
- create a database user and grant it db_reader.
I'd like to do this without granting any users elevated privileges if possible.
What I've done so far is grant the Control-M user (this is a domain user) dbcreator rights and made it owner of our copy of the database that is being refreshed.
The refresh is completing, but Control-M is not able to log onto the database to create the user.
What is the best way to accomplish this task without granting the control-m user sysadmin rights?
Would I be able to do it if I used a SQL Agent job for the restore and user creation?
System Administrator Privilege
Jan 25, 2007In SQL 2000, how do I grant system administrator privilege to "sa" or a Windows User?
View 3 Replies View RelatedError 4606 Granted Or Revoked Privilege
May 31, 2007I am recieving error 4606 Granted or revoked privilege SELECT is not compatiable with object.
Canot read the next data row for the dataset BEEP.
However when I use the same userid/pwsd in Crystal to access the same stored proceedure I have no problems accessing the data.
We have already tried re-granting the permissions to the userid to no avail.
Are there any other suggestions?
Phread
Log Reader Agent Lacks A Required Privilege
May 12, 2006I'm using our development ID to set up peer-to-peer replication. I am following the steps outlined in the BOL using SQL Studio 2005. When I created the initial publication, I read the section on permissions and assigned the development login ID db_owner. Indeed, to eliminate any possible issues (I thought), I gave it db_datareader, db_datawriter, and all the db admin rights and made very sure the db_denydatareader and db_denydatawriter roles were not checked. I did this through the Logins section of the Security folder and applied the same rights to both the distribution and the publication database. Nevertheless, when I run the LogReader agent job, it fails with the message for step 2 that:
Executed as user: ourdomsqldev. A required privilege is not held by the client. The step failed.
The dev ID essentially has sa rights to the server and all the databases. It is also a domain authenticated ID. Any ideas what's causing this problem? I really need to get the replication going reliably before the weekend is out.
Thanks.
Randy
Insufficient Privilege To Deploy Report Model Project
May 12, 2008An user member of users,what's the sufficient privileges?
View 2 Replies View RelatedSubscriber Error: A Required Privilege Is Not Held By The Client
Aug 20, 2007I keep getting this error message on the subscription execution, even though the account is a domain administrator. Both publisher and subscriber are on the same domain. Can someone tell me what is the problem?
Message
Replication-Replication Distribution Subsystem: agent [Agent Name] failed. Executed as user: [Domain]administrator. A required privilege is not held by the client. The step failed.
Regards,
Please Help: A Required Privilege Is Not Held By The Client. The Step Failed.
Apr 3, 2007 I am getting this error message "Replication-agentclassname: agent F1TESTSQLSERVER20-ELC-ELC_Pub-F1TESTSQLExpress-39 failed. Executed as user: . A required privilege is not held by the client. The step failed." All the users replication agents have failed and I am looking on how to resolve this serious error. Where do I start? I am using merge replication.
Thanks!
SAM
Vb.net Create User
Dec 18, 2007I am trying to create a program that user inputs the sa password and it creates a user in SQL Express 2005 installation. Is this possible? If so can someone point me in the right direction?
View 5 Replies View RelatedCreate User
Apr 28, 2000Hello,
I would like to create a new database user using T-Sql command. Somebody Knows?
Thanks,
Hugo Venturini
Create User Help
Jan 25, 2007hi,
i am new to mssqlserver 2005, i installed it successfully in my localhost, problem is i cant create user it gives me:
'my_aro' is not a valid login or you do not have permission
how can i create a user?
Create A RO User For All DBs
Feb 2, 2007In SQL 2005, I want to create a RO_user with read only access rights to some of my databases. I wrote the folloiwng code but get an error in USE @db_name.
Is there an easier way to create the user id and grant the RO access right to it? I have about 500 databases.
USE [Master]
GO
DECLARE @db_name nvarchar(50)
DECLARE db_cursor CURSOR FOR
SELECT master.dbo.[name]
FROM sysdatabases
WHERE Substring(name,1,4) in ('DB06', 'AC06', 'RE07')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
USE @db_name
CREATE USER [RO_user] FOR LOGIN [RO_user]
EXEC sp_addrolemember N'db_datareader', N'RO_user'
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Canada DBA
Create A New User
Jul 20, 2005Hello,How can I create a new user with password for the MS SQL - Server 2000(and the MSDE) with a SQL - Statement? I use Borland Delphi 5 with theADO - Components!Thanks.best regards.Stephan Jahrling
View 1 Replies View RelatedCreate A New User
Mar 15, 2007hi
im unable to create a new sql server authentication login
It is appreciated if anyone helps me
thanks in advance
How To Create New User?
Jan 7, 2007Hi,
i'm using SQL server 2005 and I need to create new login/user. Previously I used MySQL where user management is much simplier, so I need a help with SQL server. I run following script:
EXEC sp_addlogin 'uzivatele', @passwd = 'xyz', @defdb = 'master', @deflanguage = 'Czech'
GO
EXEC sp_addsrvrolemember 'uzivatele', 'sysadmin'
GO
GRANT CONNECT SQL TO [uzivatele]
GO
CREATE USER [jirka]
FOR LOGIN [uzivatele]
GO
But when I try to connect o database "master" as user "jirka" with password "xyz", it fails with message "Login failed for user 'jirka'". What do I wrong?
thanks
Jiri Matejka
Create User
Jun 10, 2007Hi,
how can i create a user in a stored procedure. the user has to be able to connect to my database
thanks.
Create User Scripts
Aug 21, 2007i need a scripts which should create a user with dbo privileges on the particular database please help
View 2 Replies View RelatedCreate User Form
Oct 7, 2007hello there,
Can i create user forms for user to select options and then present them with data based on their selection.i want to be able to use check boxs and combo box.thanks in advance
cheers
zolf
Create User Error!
Jan 30, 2006I have just managed to have JDBC working, but I am getting an error that the user does not exist. I have read in the MSDN help that I need to set the sqlExpress to accept SQL server authentication and not windows authentication. So when I am trying to create a new User using Microsoft SQL server management studio express I get the following error.
TITLE: Microsoft SQL Server Management Studio Express
------------------------------
Create failed for Login 'adam'. (Microsoft.SqlServer.Express.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
------------------------------
The MUST_CHANGE option is not supported by this version of Microsoft Windows. (Microsoft SQL Server, Error: 15195)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=15195&LinkId=20476
HOW To Create An SQL User Using Parameters ?
Oct 1, 2007Hello !
I want to create DB users with an stored procedure, and pass the user name as a parameter.
I wrote this code :
CREATE PROCEDURE [dbo].[spTest]
(
@ComName varchar(20),
)
AS
BEGIN
SET NOCOUNT OFF;
CREATE USER [@ComName] WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;
END
If I execute this stored procedure :
DECLARE @return_value int
EXEC @return_value = [dbo].[spTest]
@ComName = N'Test_User',
SELECT 'Return Value' = @return_value
I obtain in my DB an user called "@ComNane" instead of "Test_User" !
Where is my mystake ?
Many thanks for any help.
Luc
Create User Form
Oct 7, 2007hello there,
Can i create user forms for user to select options and then present them with data based on their selection.i want to be able to use check boxs and combo box.thanks in advance
cheers
zolf
Create A List If User
Jun 1, 2007Hi there,
Can someone please help me how to generate the list of all "user" in a database and it's access role? really need it . .
Thanks
Name In CREATE USER Statement?
Mar 27, 2008
Hi.
I was wondering if I could have a query like this:
CREATE USER 'Firstname Lastname' FOR LOGIN 'DOMAINuser' WITH DEFAULT_SCHEMA=[dbo]
The combination of firstname and lastname does not seem to work.
I have been using:
sp_grantdbaccess 'DOMAINuser', 'Firstname Lastname'
but i cannot specify DEFAULT_SCHEMA with that sp.
Any suggestions?
Integration Services :: Required Privilege Is Not Held By Client In SSIS With Proxy Account
Jun 16, 2015I running SSIS package job without sql agent , it is working fine.when i am running through sql agent not running.
created Proxy accountÂ
job failed and give above error.
Server is cluster and taking data from desktop.
server is in one domain and desktop in another domain.
Address Windowing Extensions (AWE) Requires The 'lock Pages In Memory' Privilege Which Is Not Currently Present In The Access To
Feb 6, 2008I've read all the posts... set the security policy (for the service account running SQL Server) to lock pages in memory... ran gpupdate... still same error... HELP !!!!
View 1 Replies View Related