February 21, 2008 at 8:04 am
Is it possible to grab permissions (roles/privs) of a user from a single point? Meaning, if I want to find out what DB/roles/privs a user has on a server, do I need to cnnect to every DB on that server (from a scripting t-sql standpoint)
February 21, 2008 at 10:18 am
Yes, you do. Permissions are stored in the database, not the server.
February 22, 2008 at 2:14 pm
Check if this script helps. I found it for long time back on net and was useful to me a lot of time.
---------------------------------------------------------------------------------------------
create table #DBUSERS (DBNAME varchar(50),USERNAME varchar(50), MEMBERNAME varchar(50), OBJECTNAME varchar(100), PERMGRANTED varchar(200), PERMDENIED varchar(50),COLUMNPERM varchar(10))
declare @cmd nvarchar(4000)
declare @cmd1 nvarchar(4000)
set @cmd =
'insert into #DBUSERS (DBNAME,USERNAME,MEMBERNAME)
select s.name,a.name, b.name from ?..sysusers a, ?..sysusers b, ?..sysmembers c,master..sysdatabases s
where a.uid = c.memberuid and c.groupuid = b.uid and s.name like ''?'''
set @cmd1 =
'insert into #DBUSERS (DBNAME,USERNAME,OBJECTNAME,PERMGRANTED,PERMDENIED,COLUMNPERM)
select s.name,a.name,c.name,b.actadd,b.actmod,case when (b.seladd is not null or b.selmod is not null or b.updadd is not null or b.updmod is not null or b.refadd is not null or b.refmod is not null) then ''Y'' else ''N'' end
from ?..sysusers a, ?..syspermissions b, ?..sysobjects c,master..sysdatabases s
where a.uid = b.grantee
and b.[id] = c.[id] and b.grantee <> 0
and s.name like ''?'''
print @cmd1
exec sp_MSforeachdb @command1 = @cmd,@command2 = @cmd1
select distinct USERNAME,DBNAME,MEMBERNAME from #DBUSERS order by 2
drop table #DBUSERS
SQL DBA.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply