June 26, 2003 at 9:07 am
Is there a procedure that I can use that will return all the permissions for a given Role? I want to see for instance the view and permissions assigned to it.
June 26, 2003 at 9:47 am
If it is a server fixed role you can use ‘sp_srvrolepermission’ to see the list of permissions, and for the database fixed role ‘sp_dbfixedrolepermission’. But unfortunately there is no function to see the assigned permissions to a user-defined role.
Shas3
June 26, 2003 at 11:53 am
I just created some interesting SP that gets as argument the role or user login and returns ALL permissions for that role/user into a table
It is in testing but till now I am 100% works fine.
It is kind of big but I can publish it if you want
Regards LMT
June 26, 2003 at 12:28 pm
tulcanla,
Can you please post it here?
June 26, 2003 at 1:34 pm
what id soes:
creates a table in another database, le't say Security with the name of the user attached of the Rights_ prefix...
Then runs.
PLease read and ADAPT the script!/The script wil be run in the database in which the users must bne analised.But I have a more complex form in master... anayways that is the tested workable form
Good luck!
/*****************************************/
/****************************************/
CREATE PROCEDURE DETECT_USER_ROLE_PERMISSIONS @user varchar(20)
AS
SET NOCOUNT ON
DECLARE @parameter varchar(20)
CREATE TABLE [dbo].[#temp_user_role] (
[DBFixedRole] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Permission] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_role] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[obj_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[owner] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type_action] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[action_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
SET NOCOUNT ON
declare @SQLString nvarchar(1000)
DECLARE @ParmDefinition nVARCHAR(20)
SET @SQLString =
N'drop table dw_model.dbo.Rights_'+@user
SET @ParmDefinition=N'@user varchar(20)'
SET @Parameter=@user
EXEC sp_executesql @SQLString, @ParmDefinition,@user=@Parameter
SET @SQLString =
N'CREATE TABLE dw_model.dbo.RIGHTS_'+ @user+' (
[DBFixedRole] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Permission] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[user_role] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[obj_name] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[owner] [nvarchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type_action] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[action_] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[type] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Comments] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]'
SET @ParmDefinition=N'@user varchar(20)'
SET @Parameter=@user
EXEC sp_executesql @SQLString, @ParmDefinition,@user=@Parameter
if(select count(*) from master..syslogins where name=@user and isntname=1)<>0
begin
insert into #temp_user_role(comments)
values('the user' + @user +'is an NT login')
end
if(select count(*) from master..syslogins where name=@user and isntgroup=1)<>0
begin
insert into #temp_user_role(comments)
values('the user ' + @user +'is an NT group login')
end
if(select count(*) from master..syslogins where name=@user and sysadmin=1)<>0
begin
insert into #temp_user_role(comments)
values('the user ' + @user +' has SYSADMIN fixed server role therefore
it can perform ANY activity in SQL server!!!')
end
if(select count(*) from master..syslogins where name=@user and securityadmin=1)<>0
begin
insert into #temp_user_role(comments)
values( 'the user ' + @user +' has SECURITADMIN fixed server role =
Can manage logins and CREATE DATABASE permissions, also read error logs and change passwords.!!!')
end
if(select count(*) from master..syslogins where name=@user and serveradmin=1)<>0
begin
insert into #temp_user_role(comments)
values( 'the user ' + @user +' has SERVERADMIN fixed server role =
Can set server wide configuration options, shut down the server.!!!')
end
if(select count(*) from master..syslogins where name=@user and setupadmin=1)<>0
begin
insert into #temp_user_role(comments)
values('the user ' + @user +' has SETUPADMIN fixed server role =
Can manage linked servers and startup procedures.!!!')
end
if(select count(*) from master..syslogins where name=@user and processadmin=1)<>0
begin
insert into #temp_user_role(comments)
values('the user ' + @user +' has PROCESSADMIN fixed server role =
Can manage linked servers and startup procedures.!!!')
end
if(select count(*) from master..syslogins where name=@user and diskadmin=1)<>0
begin
insert into #temp_user_role(comments)
values( 'the user ' + @user +' has DISKADMIN fixed server role =
Can manage disk files.!!!')
end
if(select count(*) from master..syslogins where name=@user and bulkadmin=1)<>0
begin
insert into #temp_user_role(comments)
values('the user ' + @user + ' has BULKADMIN fixed server role =
Can execute BULK INSERT statements.!!!')
end
if(select count(*) from master..syslogins where name=@user and dbcreator=1)<>0
begin
insert into #temp_user_role(comments)
values( 'the user ' + @user + ' has DBCREATOR fixed server role =
Creates databases(or has created a database in your server ).!!!')
end
if(select count(*)
from sysmembers a,
sysusers b
where user_name(a.memberuid)=@user
and b.issqlrole=1
and b.uid < 16393
and user_name(a.groupuid)=user_name(b.uid))> 0 -- database fixed role
BEGIN
insert into #temp_user_role(comments)
values( 'the user ' + @user + ' has database fixed roles rights in '+ db_name())
create table #server_roles
(DBFixedRole varchar(20),
Permission varchar(1000))
/* if the user is a db_owner */
if(select count(*)
from sysmembers a,
sysusers b
where user_name(a.memberuid)=@user
and b.issqlrole=1
and b.uid = 16384
and user_name(a.groupuid)=user_name(b.uid))> 0 -- database fixed role
BEGIN
PRINT 'user is part of db_owner database fixed role'
INSERT INTO #server_roles EXEC sp_executesql N'sp_dbfixedrolepermission db_owner'
END
/* if the user is a db_accessadmin */
if(select count(*)
from sysmembers a,
sysusers b
where user_name(a.memberuid)=@user
and b.issqlrole=1
and b.uid = 16385
and user_name(a.groupuid)=user_name(b.uid))> 0 -- database fixed role
BEGIN
PRINT 'user is part of db_accessadmin database fixed role'
INSERT INTO #server_roles EXEC sp_executesql N'sp_dbfixedrolepermission db_accessadmin'
END
/* if the user is a db_securityadmin */
if(select count(*)
from sysmembers a,
sysusers b
where user_name(a.memberuid)=@user
and b.issqlrole=1
and b.uid = 16386
and user_name(a.groupuid)=user_name(b.uid))> 0 -- database fixed role
BEGIN
PRINT 'user is part of db_securityadmin database fixed role'
INSERT INTO #server_roles EXEC sp_executesql N'sp_dbfixedrolepermission db_securityadmin'
END
/* if the user is a db_ddladmin */
if(select count(*)
from sysmembers a,
sysusers b
where user_name(a.memberuid)=@user
and b.issqlrole=1
and b.uid = 16387
and user_name(a.groupuid)=user_name(b.uid))> 0 -- database fixed role
BEGIN
print 'user is part of database fixed role db_ddladmin'
INSERT INTO #server_roles EXEC sp_executesql N'sp_dbfixedrolepermission db_ddladmin'
END
/* if the user is a db_backupoperator */
if(select count(*)
from sysmembers a,
sysusers b
where user_name(a.memberuid)=@user
and b.issqlrole=1
and b.uid = 16389
and user_name(a.groupuid)=user_name(b.uid))> 0 -- database fixed role
BEGIN
print 'user is part of database fixed role db_backupoperator'
INSERT INTO #server_roles EXEC sp_executesql N'sp_dbfixedrolepermission db_backupoperator'
END
/* if the user is a db_datareader */
if(select count(*)
from sysmembers a,
sysusers b
where user_name(a.memberuid)=@user
and b.issqlrole=1
and b.uid = 16390
and user_name(a.groupuid)=user_name(b.uid))> 0 -- database fixed role
BEGIN
print 'user is part of database fixed role db_datareader'
INSERT INTO #server_roles EXEC sp_executesql N'sp_dbfixedrolepermission db_datareader'
END
/* if the user is a db_datawriter */
if(select count(*)
from sysmembers a,
sysusers b
where user_name(a.memberuid)=@user
and b.issqlrole=1
and b.uid = 16391
and user_name(a.groupuid)=user_name(b.uid))> 0 -- database fixed role
BEGIN
print 'user is part of database fixed role db_datawriter'
INSERT INTO #server_roles EXEC sp_executesql N'sp_dbfixedrolepermission db_datawriter'
END
/* if the user is a db_denydatareader */
if(select count(*)
from sysmembers a,
sysusers b
where user_name(a.memberuid)=@user
and b.issqlrole=1
and b.uid = 16392
and user_name(a.groupuid)=user_name(b.uid))> 0 -- database fixed role
BEGIN
print 'user is part of database fixed role db_denydatareader'
INSERT INTO #server_roles EXEC sp_executesql N'sp_dbfixedrolepermission db_denydatareader'
END
insert into #temp_user_role(DBFixedRole,Permission)
select DBFixedRole,Permission from #server_roles
drop table #server_roles
END
/* detect uesr roles */
if(select count(*)
from sysmembers a,
sysusers b
where user_name(a.memberuid)=@user
and b.issqlrole=1
and b.uid > 16393
and user_name(a.groupuid)=user_name(b.uid))> 0
BEGIN
insert into #temp_user_role(user_role)
values(@user +' has the roles ')
insert into #temp_user_role(user_role,obj_name,owner,type_action,action_,type)
values('------------------------------','---------------------------','----','---','------','--')
insert into #temp_user_role(user_role,obj_name,owner,type_action,action_,type)
select ltrim(rtrim(user_name(p.uid)))as user_role ,
ltrim(rtrim(o.name)) as obj_name,
ltrim(rtrim(user_name(o.uid))) as owner,
ltrim(rtrim(type.name)) as type_action,
ltrim(rtrim(action.name)) as action_,
ltrim(rtrim(o.type)) as type
from sysprotects p
inner join sysobjects o
on p.id = o.id
inner join master.dbo.spt_values type
on p.protecttype = type.number
inner join master.dbo.spt_values action
on p.action = action.number
inner join(select
cast(user_name(groupuid) as varchar(50)) as group_
from sysmembers
where user_name(memberuid) not in ('dbo','guest')
and user_name(memberuid)=@user)temp_
on user_name(p.uid)=temp_.group_
where type.type = 'T'
and action.type = 'T'
order by o.type, o.name
insert into #temp_user_role(user_role,obj_name,owner,type_action,type)
values('-----------------------','-----------------','----','---','--')
END
-- user rights
insert into #temp_user_role(user_role)
values(@user +' individual rights')
insert into #temp_user_role(user_role,obj_name,owner,type_action,type)
values('-----------------------','-----------------','----','---','--')
insert into #temp_user_role(user_role,obj_name,owner,type_action,action_,type)
select ltrim(rtrim(user_name(p.uid)))as user_role ,
ltrim(rtrim(o.name)) as obj_name,
ltrim(rtrim(user_name(o.uid))) as owner,
ltrim(rtrim(type.name)) as type_action,
ltrim(rtrim(action.name)) as action_,
ltrim(rtrim(o.type))
from sysprotects p
inner join sysobjects o
on p.id = o.id
inner join master.dbo.spt_values type
on p.protecttype = type.number
inner join master.dbo.spt_values action
on p.action = action.number
where type.type = 'T'
and action.type = 'T'
and user_name(p.uid)=@user
UNION
select user_name(p.uid)as user_ ,
o.name as obj_name,
user_name(o.uid) as owner,
type.name as type_action,
action.name as action_,
o.type
from sysprotects p
inner join sysobjects o
on p.id = o.id
inner join master.dbo.spt_values type
on p.protecttype = type.number
inner join master.dbo.spt_values action
on p.action = action.number
where type.type = 'T'
and action.type = 'T'
and user_name(p.uid)='public'
insert into #temp_user_role(user_role,obj_name,owner,type_action,type)
values('-----------------------','-----------------','----','---','--')
if(select count(*)from sysmembers m where exists(select * from sysusers u where user_name(uid)=@user and m.groupuid=u.uid))>0
BEGIN
declare @member varchar(20)
DECLARE member_rights CURSOR FOR
select user_name(memberuid)
from sysmembers m
where exists(
select *
from sysusers u
where user_name(uid)=@user
and m.groupuid=u.uid)
for read only
OPEN member_rights
FETCH NEXT FROM member_rights into @member
while @@FETCH_STATUS=0
BEGIN
if(select count(*) from sysmembers where user_name(groupuid)=@member)>0
BEGIN
insert into #temp_user_role(user_role)
values(@user + ' group contains '+ @member+' group')
insert into #temp_user_role(user_role,obj_name,owner,type_action,type)
values('-----------------------','-----------------','----','---','--')
INSERT INTO #temp_user_role(user_role,obj_name,owner,type_action,action_,type)
select ltrim(rtrim(user_name(p.uid)))as user_role ,
ltrim(rtrim(o.name)) as obj_name,
ltrim(rtrim(user_name(o.uid))) as owner,
ltrim(rtrim(type.name)) as type_action,
ltrim(rtrim(action.name)) as action_,
ltrim(rtrim(o.type))as type
from sysprotects p
inner join sysobjects o
on p.id = o.id
inner join master.dbo.spt_values type
on p.protecttype = type.number
inner join master.dbo.spt_values action
on p.action = action.number
where type.type = 'T'
and action.type = 'T'
and user_name(p.uid)=@member
insert into #temp_user_role(user_role,obj_name,owner,type_action,type)
values('-----------------------','-----------------','----','---','--')
END
else
BEGIN
insert into #temp_user_role(user_role)
values(@user + ' group contains '+ @member+' as member')
insert into #temp_user_role(user_role,obj_name,owner,type_action,type)
values('-----------------------','-----------------','----','---','--')
INSERT INTO #temp_user_role(user_role,obj_name,owner,type_action,action_,type)
select ltrim(rtrim(user_name(p.uid)))as user_role ,
ltrim(rtrim(o.name)) as obj_name,
ltrim(rtrim(user_name(o.uid))) as owner,
ltrim(rtrim(type.name)) as type_action,
ltrim(rtrim(action.name)) as action_,
ltrim(rtrim(o.type)) as type
from sysprotects p
inner join sysobjects o
on p.id = o.id
inner join master.dbo.spt_values type
on p.protecttype = type.number
inner join master.dbo.spt_values action
on p.action = action.number
where type.type = 'T'
and action.type = 'T'
and user_name(p.uid)=@member
insert into #temp_user_role(user_role,obj_name,owner,type_action,type)
values('-----------------------','-----------------','----','---','--')
END
FETCH NEXT FROM member_rights into @member
END
CLOSE member_rights
DEALLOCATE member_rights
END
SET @SQLString =
N'insert into dw_model.dbo.RIGHTS_'+@user+'
select * from #temp_user_role'
SET @ParmDefinition=N'@user varchar(20)'
SET @Parameter=@user
EXEC sp_executesql @SQLString, @ParmDefinition,@user=@Parameter
print 'table created for user '+@user
drop table #temp_user_role
GO
regards
LMT
June 26, 2003 at 1:36 pm
I am sorry for all my spelling mistakes from before the script 🙂
June 26, 2003 at 1:36 pm
Thanks, that would be very helpful.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply