How can I set user roles specific database through TSQL

Question

How can I set user roles specific database through TSQL

sanuj

SSC-Addicted

Points: 438
More actions
December 14, 2008 at 3:31 am

#72312

How can I set user roles specific database through TSQL?
ie. Suppose I have 10 DBs in an SQL Server instance.
I want to set some users with sysadmin roles to 3 particular DBs among them. How can it be possible through TSQL as I am using a stored procedure to assign users to different roles
Please help me...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Sysadmin is a server level role and not a database level, so you can’t add users to this role and grant them all sysadmin’s rights on 3 databases only. My guess is that you meant to add them to db_dataowner database role. This brings up the question – Are you sure that it is needed? Are those users going to do tasks such as Creating other users, backing up and restoring the database, create/drop/modify database’s objects, read every table\view, modify data at all tables\views, execute any stored procedure that exists in the database, etc? If the answer to all of those question is true, then you can run this statement:

Exec sp_addrolemember 'db_owner', 'UserName'

If the users don’t have to perform all those tasks, it is better to add them to a role that has less privileges or to create a new role for them.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sanuj SSC-Addicted Points: 438 More actions · Answer 2

Hi Adi,

Thanks for your reply. How can we specify the DB in the query, in case if I am using dbOwner role ?

hemanth.damecharla SSCommitted Points: 1740 More actions · Answer 3

[font="Verdana"]'sp_addrolemember' does not take DBname as a parameter. You have to execute the script in each db, something like:

USE SMS

GO

EXEC sp_grantdbaccess 'Services\RThomas', 'RThomas'

GO

EXEC sp_addrolemember 'Services_DBA', 'RThomas'

[/font]

-Hope is a heuristic search :smooooth: _~Hemanth

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 4

sanuj (12/14/2008)
Hi Adi,
Thanks for your reply. How can we specify the DB in the query, in case if I am using dbOwner role ?

I see that you already got the answer, but I still recommend that you will not make them members of db_owner role. Give them the permissions that they need, not the permission that will let them do what ever they want to do within your DB.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

sanuj SSC-Addicted Points: 438 More actions · Answer 5

sanuj

SSC-Addicted

Points: 438

December 15, 2008 at 4:23 am

#912370

Hi Thanks... This worked 🙂