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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy