March 18, 2004 at 10:31 am
I have a need to find out the permissions of each of the sql users in our sql servers. I can use enterprise manager and see if they are in a role or if they are the dbo, but what I need is to use a query to display the permissions they have been granted. Doe anyone have such a query?
Thanks
Gary
March 18, 2004 at 10:44 am
This one puts the output into SQL commands that you can cut and paste into a new query and execute.
-- af_revPermissions
-- Verify existence of af_revPermissions procedure. If it does exist,
-- drop it, so that it can be re-created.
if exists (select * from sysobjects where id = object_id(N'[dbo].[af_revPermissions]') and
OBJECTPROPERTY(id, N'IsProcedure') = 1)
begin
print 'dropping existing af_revPermissions procedure.'
drop procedure [dbo].[af_revPermissions]
print 'creating af_revPermissions procedure.'
end
GO
create procedure af_revPermissions
@login_name sysname = null,
@db_parm sysname = null
as
/*************************************************************/
--
-- Module Name: af_revPermissions
--
-- Description:
-- This procedure accepts two parameters, @login_name and @db_parm.
-- If a login_name is passed into the procedure, execution will
-- be for that login_name only. Otherwise, the procedure
-- will execute for all login_names.
-- If a database name is passed in @db_parm, execution will be
-- for that database only. Otherwise, all databases on the server
-- will be processed.
-- The purpose of this procedure is to provide 'grant' and
-- 'deny' statements that can be used to reapply permissions
-- in the given database.
--
-- Written By: Steve Phelps
--
-- Date: July 15, 2003
--
-- Modified : Steve Phelps
-- Date: Sep 12, 2003
-- Modified the @column edits to include a null value.
--
-- USAGE:
--
-- use pubs
-- declare
-- @login_name sysname,
-- @db_parm sysname
-- set @login_name = 'progsbp'
-- set @db_parm = 'pubs'
-- exec af_revPermissions
-- @login_name,
-- @db_parm
--
/*************************************************************/
-- the following declare and set statements are for test purposes.
--
-- declare @login_name sysname,
-- @db_parm sysname
-- set @login_name = null
-- --set @login_name = 'netiq'
-- -- set @db_parm = null
-- set @db_parm = 'AXENT_Defender'
--
SET NOCOUNT ON
declare @command varchar(5000),
@limit int,
@counter int,
@object varchar(128),
@owner varchar(50),
@type char(2),
@grantee varchar(50),
@protect_type varchar(6),
@action varchar(10),
@column varchar (50),
@id int,
@num int,
@date datetime,
@timestamp varchar(50),
@tim varchar(10),
@db_name varchar(50),
@return_code int,
@db_fetch_status int,
@objects_fetch_status int,
@permissions_fetch_status int,
@flag int
set @limit = 0
set @counter = 0
set @date = getdate()
select @timestamp = convert(char(8),@date,112)
select @tim-2 = convert(char(10),@date,108)
select @tim-2 = replace(@tim,':','')
select @timestamp = @timestamp + rtrim(@tim)
--temporary table to identify objects to take over...
Create Table #Hold_Objects
( #Object_Name varchar(128),
#Type char(2),
#Owner_UID int,
#Owner_Name varchar(50))
-- temporary table to hold permissions for the object
Create Table #Permissions_Table
( #id int identity,
#Owner varchar(50),
#Object varchar(128),
#Grantee varchar(50),
#Grantor varchar(50),
#Protect_Type varchar(10),
#Action varchar(50),
#Column varchar(50))
create table #db_Table
(#db_Name sysname)
if @db_parm is null
insert #db_Table
select name
from master..sysdatabases
where name not in ('tempdb', 'model', 'pubs', 'Northwind')
else
insert #db_Table
select @db_parm
declare db_cur cursor for
select #db_Name
from #db_Table
order by #db_Name
open db_cur
fetch next from db_cur into
@db_name
set @db_fetch_status = @@fetch_status
if (@db_fetch_status = -1)
begin
close db_cur
deallocate db_cur
-- return -1
end
else
begin
while (@db_fetch_status <> -1)
begin
-- begin processing current db.
print '-- Permissions for database = ' + @db_name
-- Check database properties to ensure successful processing.
set @flag = 0
-- if (select databaseproperty(@db_name, 'isemergencymode')) = '1'
-- begin
-- print '-- * emergency mode is ON. *'
-- set @flag = @flag + 1
-- end
if (select databaseproperty(@db_name, 'isInLoad')) = '1'
begin
print '-- * database is in LOAD. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isInRecovery')) = '1'
begin
print '-- * database is in RECOVERY. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isInStandBy')) = '1'
begin
print '-- * database is in STANDBY. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isNotRecovered')) = '1'
begin
print '-- * database has FAILED RECOVERY. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isOffLine')) = '1'
begin
print '-- * database is OFFLINE. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isShutDown')) = '1'
begin
print '-- * database is SHUTDOWN. *'
set @flag = @flag + 1
end
if (select databaseproperty(@db_name, 'isSuspect')) = '1'
begin
print '-- * database is SUSPECT. *'
set @flag = @flag + 1
end
if @flag > 0
begin
print ' '
print '-- Because of the above described database condition(s),'
print '-- this database will not be processed. Skipping to'
print '-- next database.'
set @db_fetch_status = -2
end
-- end of new database check code.
if (@db_fetch_status <> -2)
begin
-- gather list of objects (tables, views and procedures).
select @command = '
use ' + @db_name + '
set nocount on
select so.name, so.type, so.uid, su.name
from ' + @db_name + '..sysobjects so
inner join ' + @db_name + '..sysusers su
on so.uid = su.uid
where so.type in (''U'', ''V'', ''P'', ''X'', ''FN'', ''IF'', ''TF'')'
insert #Hold_Objects
exec (@command)
print '/*'
Declare objects_cur cursor for
select ho.#Object_Name,
ho.#Type,
ho.#Owner_Name
from #Hold_Objects ho
order by ho.#Object_Name,
ho.#Owner_Name
open objects_cur
fetch next from objects_cur into
@object, @type, @owner
set @objects_fetch_status = @@fetch_status
if (@objects_fetch_status = -1)
begin
close objects_cur
deallocate objects_cur
-- return -1
end
while (@objects_fetch_status <> -1)
begin
if (@objects_fetch_status <> -2)
begin
print 'Checking: ' + @object
-- build command to gather permissions for current object and place them in #Permissions_Table
select @command = 'use ' + @db_name + ' exec sp_af_helprotect ' + char(39) + @object + char(39)
insert #Permissions_Table (#Owner, #Object, #Grantee, #Grantor,
#Protect_Type, #Action, #Column)
exec (@command)
end -- if (@objects_fetch_status <> -2)
fetch next from objects_cur into
@object, @type, @owner
set @objects_fetch_status = @@fetch_status
end -- while (@objects_fetch_status <> -1
close objects_cur
deallocate objects_cur
-- begin loop through permissions table to reapply permissions.
print '*/'
Declare permissions_cur cursor for
select
pt.#id,
pt.#Owner,
pt.#Object,
pt.#Grantee,
pt.#Protect_Type,
pt.#Action,
pt.#Column,
ho.#Type
from #Permissions_Table pt
inner join #Hold_Objects ho
on pt.#Object = ho.#Object_Name
-- where pt.#Grantee <> 'public'
order by pt.#Object Asc, pt.#Grantee Asc
open permissions_cur
fetch next from permissions_cur into
@id, @owner, @object, @grantee, @protect_type, @action, @column, @type
set @permissions_fetch_status = @@fetch_status
if (@permissions_fetch_status = -1)
begin
close permissions_cur
deallocate permissions_cur
-- return -1
end
else
begin
select @command = 'use ' + @db_name
print @command
while (@permissions_fetch_status <> -1)
begin
if (@permissions_fetch_status <> -2)
if @login_name is null or
@grantee = @login_name
begin
-- build statement to reapply the permissions for the current record.
-- check for all possibilities
-- the following line modified 9/12/03 sbp
if @column = '.' or @column = '(All+New)' or @column = '(All)' or @column is null
set @column = ' '
else
set @column = ' ([' + @column + ']) '
select @command = @protect_type + ' ' + @action +
' on ' + '[' + @db_name + ']' + '.' + @owner + '.' + '[' + @object + ']' + @column +
' to ' + '[' + @grantee + ']'
if @protect_type = 'deny'
set @command = @command + ' cascade'
print @command
select @counter = @counter + 1
end -- if @login_name is null or...
fetch next from permissions_cur into
@id, @owner, @object, @grantee, @protect_type, @action, @column, @type
set @permissions_fetch_status = @@fetch_status
end -- while (@permissions_fetch_status <> -1)
close permissions_cur
deallocate permissions_cur
end -- (@permissions_fetch_status = -1)
if @counter = 0
if @login_name is not null
print '-- ' + @login_name + ' has no object level permissions assigned in this database.'
else
print '-- There are no object level permissions assigned in this database.'
end -- if (@db_fetch_status <> -2)
truncate table #Hold_Objects
truncate table #Permissions_Table
print 'GO'
print '-- End of processing for Database = ' + @db_name
print '-- -------------------------------------------------------------------------------------'
fetch next from db_cur into
@db_name
set @db_fetch_status = @@fetch_status
end -- while (@db_fetch_status <> -1)
close db_cur
deallocate db_cur
end -- (@db_fetch_status = -1)
-- cleanup
drop table #Hold_Objects
drop table #Permissions_Table
drop table #db_Table
GO
March 18, 2004 at 11:46 am
hoo-t, first off thank you.
In the script you provided you call sp_af_helprotect and apparently, by the results I got, you have modified the sp_helprotect that comes with sql. If I am correct, what modifications did you make?
March 18, 2004 at 11:57 am
I'm sorry. I glanced through to make sure no "special" procedures were being called, and I missed it. sp_helprotect doesn't do functions. I have modified the code so that it DOES work for functions, including scalar functions, table functions, and inlined table functions.
Also, I needed it to NOT raise an error if no permissions are found. So I changed that as well.
You will need to put it in the master database.
Here it is...
CREATE PROCEDURE sp_af_helprotect
@name ncharacter varying(776) = NULL
,@username sysname = NULL
,@grantorname sysname = NULL
,@permissionarea character varying(10) = 'o s'
as
-- The following declares and sets are for testing purposes only.
-- declare
-- @name ncharacter varying(776),
-- @username sysname,
-- @grantorname sysname,
-- @permissionarea character varying(10)
--
-- -- The following sets are the default values.
-- set @name = NULL
-- set @username = NULL
-- set @grantorname = NULL
-- set @permissionarea = 'o s'
--
-- -- test values -
-- set @name = 'fn_helpcollations'
-- set @name = 'dtproperties'
/****************************************************************
sp_af_helprotect is an almost exact duplicate of sp_helprotect
in the master database. The only difference is that in
sp_helprotect, if no permissions are found for an object,
an error is raised. I needed to be able to run the procedure
without errors being raised where there were no permissions.
I have commented out the raiserror statement, and replaced it
with a print statement, and changed the return code from 1 to 0.
Whoa!!! Hold the phone! This just in!!
Microsoft documentation lists only tables, views, stored procedures, and
extended stored procedures as "objects" that this procedure should be
run against. However, I thought it would be nice if it could be used
for functions as well. Tried it and it failed. With a little effort,
I was able to modify the code so that it DOES work for functions, including
scalar functions, table functions, and inlined table functions.
Steve Phelps
*****************************************************************/
/********
Explanation of the parms...
---------------------------
@name: Name of [Owner.]Object and Statement; meaning
for sysprotects.id and sysprotects.action at the
same time; thus see parm @permissionarea.
Examples- 'user2.tb' , 'CREATE TABLE', null
@username: Name of the grantee (for sysprotects.uid).
Examples- 'user2', null
@grantorname: Name of the grantor (for sysprotects.grantor).
Examples- 'user2' --Would prevent report rows which would
-- have 'dbo' as grantor.
@permissionarea: O=Object, S=Statement; include all which apply.
Examples- 'o' , ',s' , 'os' , 'so' , 's o' , 's,o'
GeneMi
********/
Set nocount on
-- added 9/12/2003 by Steve Phelps.
declare
@command varchar(8000)
Declare
@vc1 sysname
,@Int1 integer
Declare
@charMaxLenOwner character varying(11)
,@charMaxLenObject character varying(11)
,@charMaxLenGrantee character varying(11)
,@charMaxLenGrantor character varying(11)
,@charMaxLenAction character varying(11)
,@charMaxLenColumnName character varying(11)
Declare
@OwnerName sysname
,@ObjectStatementName sysname
/* Perform temp table DDL here to minimize compilation costs*/
CREATE Table #t1_Prots
( Id int Null
,Type1Code char(6) collate database_default NOT Null
,ObjType char(2) collate database_default Null
,ActionName varchar(20) collate database_default Null
,ActionCategory char(2) collate database_default Null
,ProtectTypeName char(10) collate database_default Null
,Columns_Orig varbinary(32) Null
,OwnerName sysname collate database_default NOT Null
,ObjectName sysname collate database_default NOT Null
,GranteeName sysname collate database_default NOT Null
,GrantorName sysname collate database_default NOT Null
,ColumnName sysname collate database_default Null
,ColId smallint Null
,Max_ColId smallint Null
,All_Col_Bits_On tinyint Null
,new_Bit_On tinyint Null) -- 1=yes on
/* Check for valid @permissionarea */
Select @permissionarea = upper( isnull(@permissionarea,'?') )
IF ( charindex('O',@permissionarea) <= 0
AND charindex('S',@permissionarea) <= 0)
begin
raiserror(15300,-1,-1 ,@permissionarea,'o,s')
return (1)
end
select @vc1 = parsename(@name,3)
/* Verified db qualifier is current db*/
IF (@vc1 is not null and @vc1 <> db_name())
begin
raiserror(15302,-1,-1) --Do not qualify with DB name.
return (1)
end
/* Derive OwnerName and @ObjectStatementName*/
select @OwnerName = parsename(@name, 2)
,@ObjectStatementName = parsename(@name, 1)
IF (@ObjectStatementName is NULL and @name is not null)
begin
raiserror(15253,-1,-1,@name)
return (1)
end
/* Copy info from sysprotects for processing */
IF charindex('O',@permissionarea) > 0
begin
/* Copy info for objects */
INSERT #t1_Prots
( Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,Columns_Orig
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId
,Max_ColId
,All_Col_Bits_On
,new_Bit_On)
/* 1Regul indicates action can be at column level,
2Simpl indicates action is at the object level */
SELECT id
,case
when columns is null then '2Simpl'
else '1Regul'
end
,Null
,val1.name
,'Ob'
,val2.name
,columns
,user_name(objectproperty( id, 'ownerid' ))
,object_name(id)
,user_name(uid)
,user_name(grantor)
,case
when columns is null then '.'
else Null
end
,-123
,Null
,Null
,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,master.dbo.spt_values val2
where (@OwnerName is null or user_name(objectproperty( id, 'ownerid' )) = @OwnerName)
and (@ObjectStatementName is null or object_name(id) = @ObjectStatementName)
and (@username is null or user_name(uid) = @username)
and (@grantorname is null or user_name(grantor) = @grantorname)
and val1.type = 'T'
and val1.number = sysp.action
and val2.type = 'T' --T is overloaded.
and val2.number = sysp.protecttype
and sysp.id != 0
IF EXISTS (SELECT * From #t1_Prots)
begin
UPDATE #t1_Prots set ObjType = ob.xtype
FROM sysobjects ob
WHERE ob.id = #t1_Prots.Id
UPDATE #t1_Prots
set Max_ColId = (select max(colid) from syscolumns sysc
where #t1_Prots.Id = sysc.id) -- colid may not consecutive if column dropped
where Type1Code = '1Regul'
/* First bit set indicates actions pretains to new columns. (i.e. table-level permission)
Set new_Bit_On accordinglly */
UPDATE #t1_Prots SET new_Bit_On =
CASE convert(int,substring(Columns_Orig,1,1)) & 1
WHEN 1 then 1
ELSE 0
END
WHERE ObjType <> 'V' and Type1Code = '1Regul'
/* Views don't get new columns */
UPDATE #t1_Prots set new_Bit_On = 0
WHERE ObjType = 'V'
/* Indicate enties where column level action pretains to all
columns in table All_Col_Bits_On = 1 */
UPDATE #t1_Prots set All_Col_Bits_On = 1
where #t1_Prots.Type1Code = '1Regul'
and not exists
(select *
from syscolumns sysc, master..spt_values v
where #t1_Prots.Id = sysc.id and sysc.colid = v.number
and v.number <= Max_ColId -- column may be dropped/added after Max_ColId snap-shot
and v.type = 'P' and
/* Columns_Orig where first byte is 1 means off means on and on mean off
where first byte is 0 means off means off and on mean on */
case convert(int,substring(#t1_Prots.Columns_Orig, 1, 1)) & 1
when 0 then convert(tinyint, substring(#t1_Prots.Columns_Orig, v.low, 1))
else (~convert(tinyint, isnull(substring(#t1_Prots.Columns_Orig, v.low, 1),0)))
end & v.high = 0)
/* Indicate entries where column level action pretains to
only some of columns in table All_Col_Bits_On = 0*/
UPDATE #t1_Prots set All_Col_Bits_On = 0
WHERE #t1_Prots.Type1Code = '1Regul'
and All_Col_Bits_On is null
Update #t1_Prots
set ColumnName =
case
when All_Col_Bits_On = 1 and new_Bit_On = 1 then '(All+New)'
when All_Col_Bits_On = 1 and new_Bit_On = 0 then '(All)'
when All_Col_Bits_On = 0 and new_Bit_On = 1 then '(New)'
end
from #t1_Prots
where ObjType IN ('S ' ,'U ', 'V ')
and Type1Code = '1Regul'
and NOT (All_Col_Bits_On = 0 and new_Bit_On = 0)
/* Expand and Insert individual column permission rows */
INSERT into #t1_Prots
(Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId)
SELECT prot1.Id
,'1Regul'
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,col_name ( prot1.Id ,val1.number )
,val1.number
from #t1_Prots prot1
,master.dbo.spt_values val1
,syscolumns sysc
where prot1.ObjType IN ('S ' ,'U ' ,'V ')
and prot1.All_Col_Bits_On = 0
and prot1.Id = sysc.id
and val1.type = 'P'
and val1.number = sysc.colid
and
case convert(int,substring(prot1.Columns_Orig, 1, 1)) & 1
when 0 then convert(tinyint, substring(prot1.Columns_Orig, val1.low, 1))
else (~convert(tinyint, isnull(substring(prot1.Columns_Orig, val1.low, 1),0)))
end & val1.high <> 0
delete from #t1_Prots
where ObjType IN ('S ' ,'U ' ,'V ')
and All_Col_Bits_On = 0
and new_Bit_On = 0
end
end
/* Handle statement permissions here*/
IF (charindex('S',@permissionarea) > 0)
begin
/* All statement permissions are 2Simpl */
INSERT #t1_Prots
( Id
,Type1Code
,ObjType
,ActionName
,ActionCategory
,ProtectTypeName
,Columns_Orig
,OwnerName
,ObjectName
,GranteeName
,GrantorName
,ColumnName
,ColId
,Max_ColId
,All_Col_Bits_On
,new_Bit_On)
SELECT id
,'2Simpl'
,Null
,val1.name
,'St'
,val2.name
,columns
,'.'
,'.'
,user_name(sysp.uid)
,user_name(sysp.grantor)
,'.'
,-123
,Null
,Null
,Null
FROM sysprotects sysp
,master.dbo.spt_values val1
,master.dbo.spt_values val2
where (@username is null or user_name(sysp.uid) = @username)
and (@grantorname is null or user_name(sysp.grantor) = @grantorname)
and val1.type = 'T'
and val1.number = sysp.action
and (@ObjectStatementName is null or val1.name = @ObjectStatementName)
and val2.number = sysp.protecttype
and val2.type = 'T'
and sysp.id = 0
end
IF EXISTS (SELECT * From #t1_Prots)
begin
/* Calculate dynamic display col widths */
/* this code modified 9/12/2003 by Steve Phelps */
if (select top 1 OwnerName from #t1_Prots) is not null
select @charMaxLenOwner = convert ( varchar, max(datalength(OwnerName))) from #t1_Prots
else
set @charMaxLenOwner = 1
if (select top 1 ObjectName from #t1_Prots) is not null
select @charMaxLenObject = convert ( varchar, max(datalength(ObjectName))) from #t1_Prots
else
set @charMaxLenObject = 1
if (select top 1 GranteeName from #t1_Prots) is not null
select @charMaxLenGrantee = convert ( varchar, max(datalength(GranteeName))) from #t1_Prots
else
set @charMaxLenGrantee = 1
if (select top 1 GrantorName from #t1_Prots) is not null
select @charMaxLenGrantor = convert ( varchar, max(datalength(GrantorName))) from #t1_Prots
else
set @charMaxLenGrantor = 1
if (select top 1 ActionName from #t1_Prots) is not null
select @charMaxLenAction = convert ( varchar, max(datalength(ActionName))) from #t1_Prots
else
set @charMaxLenAction = 1
if (select top 1 ColumnName from #t1_Prots) is not null
select @charMaxLenColumnName = convert ( varchar, max(datalength(ColumnName))) from #t1_Prots
else
set @charMaxLenColumnName = 1
/* Output the report */
-- This code modified 9/12/2003 by Steve Phelps.
-- EXECUTE(
set @command =
'Set nocount off
SELECT ''Owner'' = substring (OwnerName ,1 ,' + @charMaxLenOwner + ')
,''Object'' = substring (ObjectName ,1 ,' + @charMaxLenObject + ')
,''Grantee'' = substring (GranteeName ,1 ,' + @charMaxLenGrantee + ')
,''Grantor'' = substring (GrantorName ,1 ,' + @charMaxLenGrantor + ')
,''ProtectType''= ProtectTypeName
,''Action'' = substring (ActionName ,1 ,' + @charMaxLenAction + ')
,''Column'' = substring (ColumnName ,1 ,' + @charMaxLenColumnName + ')
from #t1_Prots
order by
ActionCategory
,Owner ,Object
,Grantee ,Grantor
,ProtectType ,Action
,ColId --Multiple -123s ( <0 ) possible
Set nocount on'
exec (@command)
--
return (0) -- sp_helprotect
end
else
begin
-- This code modified 9/12/2003 Steve Phelps.
print '-- There are no matching rows on which to report.'
return (0)
-- raiserror(15330,-1,-1)
-- return (1)
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
drop table #t1_Prots
select * from #t1_Prots
*/
March 19, 2004 at 1:26 pm
Check the newsletter from Wed., 3/17. There is a tool described in the featured article section that a user posted which will export user permissions to an Excel spreadsheet. I've downloaded it and tried it and it seems to work fine.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply