Remove and Grant User permissions

Question

Remove and Grant User permissions

ColeTrain

SSC Veteran

Points: 240
More actions
February 18, 2010 at 3:10 pm

#142498

I'm going through a database consolidation. I have about 150 users that have access to 40 +/- databases we are consolidating down to one database. I need to remove all user permissions on all the databases except one and grant all users permissions to the single database.
all databases are named incrementally sqldb01 - sqldb40
all user names are domain names domain\username
I would like it if I could pull all the user's from all the sqldb's into a list and then grant the list of user's all the same rights to the single db. I could have 500 +/- domain user's so I don't want to grant everyone access to the single db. I need to grant only the user's that currently have access to the sqldb databases now.
Any help would be greatly appreceated.

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

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

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

You can write a script to extract the Users from the System Table and concatenate the SP to grant DB Access, add the user to a role, assign permissions etc.

I hope this helps.

For better, quicker answers on T-SQL questions, click on the following...
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/

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 2

Here's a start

DECLARE @PrincipalList TABLE (

PrincipalName nvarchar(255)

)

DECLARE @DbNumber int

SELECT @DbNumber = 1

PRINT 'DECLARE @PrincipalList TABLE (

PrincipalName nvarchar(255)

)'

WHILE @DbNumber <= 40

BEGIN

PRINT 'INSERT INTO @PrincipalList'

PRINT 'SELECT NAME FROM sqldb' + CONVERT(VARCHAR(2), @DbNumber) + '.sys.sysusers WHERE NAME LIKE ''%\%'''

SELECT @DbNumber += 1

END

PRINT '

SELECT ''CREATE USER ['' + PrincipalName + ''] FOR LOGIN [''+ PrincipalName + ''] WITH DEFAULT_SCHEMA=[dbo] FROM @PrincipalList'

Run that, then copy the output to a new session and run that. Should get you close.

Converting oxygen into carbon dioxide, since 1955.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Thanks for providing the script.

I'm feeling a little tired or I would have provided something.

Way to go! 🙂

For better, quicker answers on T-SQL questions, click on the following...
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/

ColeTrain SSC Veteran Points: 240 More actions · Answer 4

I greatly appreciate the assistance. I'm not very tsql savvy that is why I am reaching out for assistance. I'm currently trying to run this script and it seems to be running for a very long time.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

You can use the old way to find out what you script is doing by using sp_who2 to get the SPID and then use the DBCC INPUTBUFFER(SPID) to identify the current command being executed.

In versions prior to SQL Server 2000 the sysprocesses table was commonly used.

Now you can use the sys.dm_exec_connections DMV and

INNER JOIN with the sys.dm_exec_sessions on session_id

I would suggest that you narrow your record set by adding conditions to the WHERE Clause so that only a few records are processed. That way you have a better idea if your SP is working.

For better, quicker answers on T-SQL questions, click on the following...
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/