Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Unanswered: Need to find and document permissions in user databases

    Hi folks, i've an instance with many userdatabases. i want to use SP_HELPUSER to output all database users and roles defined to em. how come i do this through a script to view permissions in all databases. I couldn't use (USE database ) in a loop.



    Howdy!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    While there is NO GUARANTY of future support, you could use:
    Code:
    EXECUTE sp_MSForEachDatabase 'EXECUTE sp_helpuser'
    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Not sure what you want to use this for, but here is an old piece of code that I wrote and that I still use for documenting and restoring database users, logins, and roles, especially after moving a database to a new server. It creates an SQL Script for restoring the user environment, but you can tinker with it if you want to...

    Code:
    create procedure script_logins as
    
    --This script generates an SQL script for recreating the server logins, database users, and roles
    --associated with the current database.
    --When running the script through the isql command line utility, execute with the following options to ensure
    --that no output formatting is applied which would prevent the output script from being executable:
    --osql -U[login] -P[password] -S[server] -w255 -h-1 -n -d[database] -i[file path]\script_db_logins.sql -o[output file]
    --
    --The following sql statements will execute the process for all databases except tempdb:
    /*
    declare dblist cursor for select name from master..sysdatabases where name <> 'tempdb'
    declare @dbname varchar(50)
    declare @sqlstring varchar (200)
    open dblist
    FETCH NEXT FROM dblist into @dbname
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	select @sqlstring = 'osql -U[Login] -P[Password] -S[Server] -w255 -h-1 -n -d' + @dbname + ' -i[file path]\script_db_logins.sql -o[file path]\' + @dbname + '_logins.sql'
    	exec xp_cmdshell @sqlstring
    	FETCH NEXT FROM dblist into @dbname
    END
    CLOSE dblist
    DEALLOCATE dblist
    */
    --blindman 1/15/2001
    set NOCOUNT ON
    select '--Database and login recreation script generated by script_db_logins.sql on ' + convert(varchar, getdate(), 1) + char(13) + '--blindman 1/15/2001' + char(13)
    select '--DELETE ORPHANED USERS'
    select 'exec sp_configure "allow updates", 1' + CHAR(13) +
    'RECONFIGURE WITH OVERRIDE' + CHAR(13) +
    'GO' + CHAR(13) +
    'delete from sysusers' + CHAR(13) +
    'where uid between 4 and 16382 and sid is null' + CHAR(13) +
    'GO' + CHAR(13) +
    'exec sp_configure "allow updates", 0' + CHAR(13) +
    'RECONFIGURE WITH OVERRIDE' + CHAR(13) +
    'GO'
    if exists (select * from master..syslogins l, sysusers u where l.sid = u.sid and l.sid <> 0x01)
    BEGIN
    	select '--ADD SERVER LOGINS'
    	select 
    	'if not exists (select * from master..syslogins where name = ''' + l.name + ''')' + CHAR(13) +
    	'exec sp_addlogin ''' + l.name + ''', null, ''' + dbname + ''', ''' + language + '''' + CHAR(13) + 'GO' + CHAR(13) 
    	from master..syslogins l, sysusers u
    	where l.sid = u.sid and l.sid <> 0x01
    END
    ELSE	select '--NO SERVER LOGINS'
    if exists (select * from master..syslogins l, sysusers u
    	where l.sid = u.sid and l.sid <> 0x01
    	and l.sysadmin + l.securityadmin + l.serveradmin + l.setupadmin + l.processadmin + l.diskadmin + l.dbcreator >0)
    begin
    	select '--ADD SERVER ROLES'
    	if exists (select * from master..syslogins l, sysusers u where l.sid = u.sid and l.sysadmin = 1)
    	begin
    		select 'exec sp_addsrvrolemember ''' + l.loginname + ''', ''sysadmin''' + CHAR(13) + 'GO' + CHAR(13) 
    		from master..syslogins l, sysusers u
    		where l.sid = u.sid and l.sysadmin = 1 and l.sid <> 0x01
    	end
    	else	select '--NO SERVER ROLES'
    	if exists (select * from master..syslogins l, sysusers u where l.sid = u.sid and l.securityadmin = 1 and l.sid <> 0x01)
    	begin
    		select 'exec sp_addsrvrolemember ''' + l.loginname + ''', ''securityadmin''' + CHAR(13) + 'GO' + CHAR(13) 
    		from master..syslogins l, sysusers u
    		where l.sid = u.sid and l.securityadmin = 1 and l.sid <> 0x01
    	end
    	if exists (select * from master..syslogins l, sysusers u where l.sid = u.sid and l.serveradmin = 1 and l.sid <> 0x01)
    	begin
    		select 'exec sp_addsrvrolemember ''' + l.loginname + ''', ''serveradmin''' + CHAR(13) + 'GO' + CHAR(13) 
    		from master..syslogins l, sysusers u
    		where l.sid = u.sid and l.serveradmin = 1 and l.sid <> 0x01
    	end
    	if exists (select * from master..syslogins l, sysusers u where l.sid = u.sid and l.setupadmin = 1 and l.sid <> 0x01)
    	begin
    		select 'exec sp_addsrvrolemember ''' + l.loginname + ''', ''setupadmin''' + CHAR(13) + 'GO' + CHAR(13) 
    		from master..syslogins l, sysusers u
    		where l.sid = u.sid and l.setupadmin = 1 and l.sid <> 0x01
    	end
    	if exists (select * from master..syslogins l, sysusers u where l.sid = u.sid and l.processadmin = 1 and l.sid <> 0x01)
    	begin
    	select 'exec sp_addsrvrolemember ''' + l.loginname + ''', ''processadmin''' + CHAR(13) + 'GO' + CHAR(13) 
    		from master..syslogins l, sysusers u
    		where l.sid = u.sid and l.processadmin = 1 and l.sid <> 0x01
    	end
    	if exists (select * from master..syslogins l, sysusers u where l.sid = u.sid and l.diskadmin = 1 and l.sid <> 0x01)
    	begin
    		select 'exec sp_addsrvrolemember ''' + l.loginname + ''', ''diskadmin''' + CHAR(13) + 'GO' + CHAR(13) 
    		from master..syslogins l, sysusers u
    		where l.sid = u.sid and l.diskadmin = 1 and l.sid <> 0x01
    	end
    	if exists (select * from master..syslogins l, sysusers u where l.sid = u.sid and l.dbcreator = 1 and l.sid <> 0x01)
    	begin
    		select 'exec sp_addsrvrolemember ''' + l.loginname + ''', ''dbcreator''' + CHAR(13) + 'GO' + CHAR(13) 
    		from master..syslogins l, sysusers u
    		where l.sid = u.sid and l.dbcreator = 1 and l.sid <> 0x01
    	end
    end
    else select '--NO SERVER ROLES'
    if exists (select * from master..syslogins l, sysusers u where l.sid = u.sid and l.sid <> 0x01)
    begin
    	SELECT '--ADD DATABASE ACCESS'
    	select
    	'if not exists (select * from sysusers where name = ''' + u.name + ''' and uid < 16382)' + CHAR(13) +
    	'exec sp_grantdbaccess ''' + l.loginname + ''', ''' + u.name + '''' + CHAR(13) + 'go' + CHAR(13)
    	from master..syslogins l, sysusers u
    	where l.sid = u.sid and l.sid <> 0x01
    end
    else select '--NO DATABASE ACCESS'
    if exists (select * from sysusers u, sysmembers m, sysusers r where u.uid = m.memberuid and m.groupuid = r.uid)
    begin
    	SELECT '--ADD DATABASE ROLES'
    	Select 'exec sp_addrolemember ''' + r.name + ''', ''' + u.name + '''' + CHAR(13) + 'GO' + CHAR(13)
    	from sysusers u, sysmembers m, sysusers r
    	where u.uid = m.memberuid and m.groupuid = r.uid and u.name <> 'dbo'
    end
    else select '--NO DATABASE ROLES'
    
    GO
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Thanx Mr.Lindman. Really appreciate it.I'll come back to u after goin through the script.



    Howdy!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •