May 29, 2007 at 12:29 pm
Forgive my newbieness, but I assume DBAs are able to restrict users from being able to execute specified (any) stored procedures.
That said, is there any good way (e.g. using SQL and not pointing and clicking around the database) I can test a SQL Server to see who can execute a given stored procedure, for example, who can use xp_cmdshell or sp_adduser?
If you have any best practices or thoughts, please post them here!
Thanks
-Taylor
May 29, 2007 at 1:20 pm
I got a stored procedure that queries the system tables for role memberships and permissions and returns two recordsets for them respectivelty. I have this proc created in a "dba" database on my server, but feel free to tweak it to your liking.
USE
[dba]
GO
/****** Object: StoredProcedure [dbo].[dba_GetPermsAndRoles] Script Date: 05/29/2007 13:15:46 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
PROCEDURE [dbo].[dba_GetPermsAndRoles]
(
@DB varchar
(128),
@RoleOrUser varchar
(128) = '',
@TypeOfUser varchar
(50) = '',
@Object varchar
(128) = ''
)
AS
/*******************************************************************************************************
* dba.dbo.dba_GetPermsAndRoles
* Creator: Adam Wilbur
* Date: 5-17-2007
*
*
* Description: Lists user permissions based on parameters passed in. The first results set is Permissions, the second one is Role Memberships
*
* Usage:
-- For a whole database,
EXECUTE dba.dbo.dba_GetPermsAndRoles 'master'
-- For a specific object (note: @Object needs to be fully qualified)
EXECUTE dba.dbo.dba_GetPermsAndRoles 'master', '', '', 'master.dbo.xp_cmdshell'
-- For a specific user or role
EXECUTE dba.dbo.dba_GetPermsAndRoles 'master', 'testuser', '', ''
-- For a type of user
EXECUTE dba.dbo.dba_GetPermsAndRoles 'master', '', 'SQL User', ''
*
* Modifications:
* Developer Name Date Brief Description
* ------------------ -------- ------------------------------------------------------------
********************************************************************************************************/
BEGIN
set nocount on
declare @SQL varchar(8000)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpObjectPermissions'))
drop table #tmpObjectPermissions
CREATE TABLE #tmpObjectPermissions
(
[DATABASENAME]
sysname NOT NULL ,
[Role_Or_User]
sysname NOT NULL,
[Object] varchar
(200) NOT NULL,
[Object_Type] varchar
(50),
[Action] varchar
(50),
[Permission] varchar
(50),
[TypeOfUser] varchar
(30)
)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpRoles_Users'))
drop table #tmpRoles_Users
CREATE TABLE #tmpRoles_Users
(
[DATABASENAME]
sysname NOT NULL ,
[USER_NAME]
sysname NOT NULL,
[ROLE_NAME]
sysname NOT NULL
)
-- begin parameter validation
if not exists (select * from master.dbo.sysdatabases where name = @DB)
begin
print @DB + ' is not a valid database on this server'
return
end
if @TypeOfUser <> ''
begin
if @TypeOfUser not in ('SQL User', 'Role', 'NT Group', 'NT User', 'App Role')
begin
Print 'Invalid input for @TypeOfUser.... Valid values for @TypeOfUser are ''SQL User'', ''Role'', ''NT Group'', ''NT User'', ''App Role'''
Return
end
end
-- end parameter validation
select @SQL = 'insert into #tmpObjectPermissions
select '''
+
@DB
+ ''',
u.name as ''Role_Or_User'',
''Object'' = '''
+ @DB + ''' + ''.dbo.' + ''' + o.name,
''Object Type''=
case
when o.xtype=''FN'' then ''Scalar function''
when o.xtype=''IF'' then ''Inlined table-function''
when o.xtype=''P'' then ''Stored procedure''
when o.xtype=''TF'' then ''Table function''
when o.xtype=''TR'' then ''Trigger''
when o.xtype=''U'' then ''User table''
when o.xtype=''V'' then ''View''
when o.xtype=''X'' then ''Extended Stored Proc''
when o.xtype=''S'' then ''System Table''
else o.xtype
end,
''Action'' =
case
when pr.action=26 then ''REFERENCES''
when pr.action=178 then ''CREATE FUNCTION''
when pr.action=193 then ''SELECT''
when pr.action=195 then ''INSERT''
when pr.action=196 then ''DELETE''
when pr.action=197 then ''UPDATE''
when pr.action=198 then ''CREATE TABLE''
when pr.action=203 then ''CREATE DATABASE''
when pr.action=207 then ''CREATE VIEW''
when pr.action=222 then ''CREATE PROCEDURE''
when pr.action=224 then ''EXECUTE''
when pr.action=228 then ''BACKUP DATABASE''
when pr.action=233 then ''CREATE DEFAULT''
when pr.action=235 then ''BACKUP LOG''
when pr.action=236 then ''CREATE RULE''
end,
''Permission''=
case
when pr.protecttype = 204 then ''GRANT_W_GRANT''
when pr.protecttype = 205 then ''GRANT''
when pr.protecttype = 206 then ''REVOKE''
end,
''TypeOfUser'' =
case
when issqluser = 1 then ''SQL User''
when issqlrole = 1 then ''Role''
when isntuser = 1 then ''NT User''
when isntgroup = 1 then ''NT Group''
when isapprole = 1 then ''App Role''
else ''Aliased?''
end
from '
+ QuoteName(@DB) + '.dbo.sysprotects pr, ' + QuoteName(@DB) + '.dbo.sysobjects o, ' + QuoteName(@DB) + '.dbo.sysusers u
where pr.id = o.id
and pr.uid = u.uid'
execute (@SQL)
select @SQL =
'insert into #tmpRoles_Users
select N'''
+ @DB + ''', a.name, c.name
from '
+ QuoteName(@DB) + '.dbo.sysusers a
join '
+ QuoteName(@DB) + '.dbo.sysmembers b on b.memberuid = a.uid
join '
+ QuoteName(@DB) + '.dbo.sysusers c on c.uid = b.groupuid
where a.name != ''dbo'''
execute (@SQL)
-- Now build select based on optional parameters passed in
set @SQL = 'select *
from #tmpObjectPermissions
where databasename = '''
+ @DB + ''''
if @TypeOfUser <> ''
begin
set @SQL = @SQL + ' and TypeOfUser = ''' + @TypeOfUser + ''''
end
if @Object <> ''
begin
set @SQL = @SQL + ' and Object = ''' + @Object + ''''
end
if @RoleOrUser <> ''
begin
set @SQL = @SQL + ' and Role_Or_User = ''' + @RoleOrUser + ''''
end
set @SQL = @SQL + ' order by Role_Or_User, Object_Type, Object'
print @SQL
execute (@SQL)
set @SQL = 'select *
from #tmpRoles_Users
where databasename = '''
+ @DB + ''''
if @RoleOrUser <> ''
begin
set @SQL = @SQL + ' and user_name = ''' + @RoleOrUser + ''''
end
print @SQL
execute (@SQL)
end
A.J.
DBA with an attitude
May 29, 2007 at 1:41 pm
A.J.
- Absolutely awesome programming -
Any suggestions on making it such that I am not actually creating a database object and having to leave it on the server? My goal is to be able to send the jist of your code to a DBA in the UK, have him give me the output, while not creating a file / data stored the database (ideally, the results of this script would be put out to a text file and e-mailed to me). I would be running this for a database at a time and don't need to send any additional parameters to it as I can get someone to shift through the results.
It's a big no no for me to introduce code into an environment which would stay there once I am done reviewing it and I can't count on the DBA to delete or clean it up.
May 29, 2007 at 2:01 pm
Sure... just populate the "sets" with whatever your particular search is for...example below is for searching for permissions granted to xp_cmdshell. Make sure to leave the others as empty strings though.
declare
@DB varchar
(128),
@RoleOrUser varchar
(128),
@TypeOfUser varchar
(50),
@Object varchar
(128)
set
@DB = 'master'
set
@RoleOrUser = ''
set
@TypeOfUser = ''
set
@Object = 'xp_cmdshell'
declare @SQL varchar(8000)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpObjectPermissions'))
drop table #tmpObjectPermissions
CREATE TABLE #tmpObjectPermissions
(
[DATABASENAME]
sysname NOT NULL ,
[Role_Or_User]
sysname NOT NULL,
[Object] varchar
(200) NOT NULL,
[Object_Type] varchar
(50),
[Action] varchar
(50),
[Permission] varchar
(50),
[TypeOfUser] varchar
(30)
)
if exists (select [id] from tempdb..sysobjects where [id] = OBJECT_ID ('tempdb..#tmpRoles_Users'))
drop table #tmpRoles_Users
CREATE TABLE #tmpRoles_Users
(
[DATABASENAME]
sysname NOT NULL ,
[USER_NAME]
sysname NOT NULL,
[ROLE_NAME]
sysname NOT NULL
)
-- begin parameter validation
if not exists (select * from master.dbo.sysdatabases where name = @DB)
begin
print @DB + ' is not a valid database on this server'
return
end
if @TypeOfUser <> ''
begin
if @TypeOfUser not in ('SQL User', 'Role', 'NT Group', 'NT User', 'App Role')
begin
Print 'Invalid input for @TypeOfUser.... Valid values for @TypeOfUser are ''SQL User'', ''Role'', ''NT Group'', ''NT User'', ''App Role'''
Return
end
end
-- end parameter validation
select @SQL = 'insert into #tmpObjectPermissions
select '''
+
@DB
+ ''',
u.name as ''Role_Or_User'',
''Object'' = '''
+ @DB + ''' + ''.dbo.' + ''' + o.name,
''Object Type''=
case
when o.xtype=''FN'' then ''Scalar function''
when o.xtype=''IF'' then ''Inlined table-function''
when o.xtype=''P'' then ''Stored procedure''
when o.xtype=''TF'' then ''Table function''
when o.xtype=''TR'' then ''Trigger''
when o.xtype=''U'' then ''User table''
when o.xtype=''V'' then ''View''
when o.xtype=''X'' then ''Extended Stored Proc''
when o.xtype=''S'' then ''System Table''
else o.xtype
end,
''Action'' =
case
when pr.action=26 then ''REFERENCES''
when pr.action=178 then ''CREATE FUNCTION''
when pr.action=193 then ''SELECT''
when pr.action=195 then ''INSERT''
when pr.action=196 then ''DELETE''
when pr.action=197 then ''UPDATE''
when pr.action=198 then ''CREATE TABLE''
when pr.action=203 then ''CREATE DATABASE''
when pr.action=207 then ''CREATE VIEW''
when pr.action=222 then ''CREATE PROCEDURE''
when pr.action=224 then ''EXECUTE''
when pr.action=228 then ''BACKUP DATABASE''
when pr.action=233 then ''CREATE DEFAULT''
when pr.action=235 then ''BACKUP LOG''
when pr.action=236 then ''CREATE RULE''
end,
''Permission''=
case
when pr.protecttype = 204 then ''GRANT_W_GRANT''
when pr.protecttype = 205 then ''GRANT''
when pr.protecttype = 206 then ''REVOKE''
end,
''TypeOfUser'' =
case
when issqluser = 1 then ''SQL User''
when issqlrole = 1 then ''Role''
when isntuser = 1 then ''NT User''
when isntgroup = 1 then ''NT Group''
when isapprole = 1 then ''App Role''
else ''Aliased?''
end
from '
+ QuoteName(@DB) + '.dbo.sysprotects pr, ' + QuoteName(@DB) + '.dbo.sysobjects o, ' + QuoteName(@DB) + '.dbo.sysusers u
where pr.id = o.id
and pr.uid = u.uid'
execute (@SQL)
select @SQL =
'insert into #tmpRoles_Users
select N'''
+ @DB + ''', a.name, c.name
from '
+ QuoteName(@DB) + '.dbo.sysusers a
join '
+ QuoteName(@DB) + '.dbo.sysmembers b on b.memberuid = a.uid
join '
+ QuoteName(@DB) + '.dbo.sysusers c on c.uid = b.groupuid
where a.name != ''dbo'''
execute (@SQL)
-- Now build select based on optional parameters passed in
set @SQL = 'select *
from #tmpObjectPermissions
where databasename = '''
+ @DB + ''''
if @TypeOfUser <> ''
begin
set @SQL = @SQL + ' and TypeOfUser = ''' + @TypeOfUser + ''''
end
if @Object <> ''
begin
set @SQL = @SQL + ' and Object = ''' + @Object + ''''
end
if @RoleOrUser <> ''
begin
set @SQL = @SQL + ' and Role_Or_User = ''' + @RoleOrUser + ''''
end
set @SQL = @SQL + ' order by Role_Or_User, Object_Type, Object'
print @SQL
execute (@SQL)
set @SQL = 'select *
from #tmpRoles_Users
where databasename = '''
+ @DB + ''''
if @RoleOrUser <> ''
begin
set @SQL = @SQL + ' and user_name = ''' + @RoleOrUser + ''''
end
print @SQL
execute (@SQL)
A.J.
DBA with an attitude
July 16, 2007 at 8:14 am
This is a great start for what I have to do. Our security group wants a report showing that the public role doesn't have access to certain XSP's. This appears to display only the xsp's that have access assigned to the public role. What would I have to change to list even xsp's that the public role does not have access to?
Thanks,
Brian
July 16, 2007 at 11:45 pm
Include 'X'n the type clause so that it searches for extended SPs too.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 17, 2007 at 9:27 am
I have done a search for only extended SP's. However this only shows 60 items. There are 171 objects listed in the master database. I am guessing that this is only showing the objects that the public role actually has access to. I would like to see all of them, even the ones with no access. Any ideas how to change this to get that?
Thanks,
Brian
July 17, 2007 at 2:47 pm
have you tried sp_helprotect?
July 18, 2007 at 2:05 am
sp_helprotect will not be useful. Can you list the extended procedures and check what is the xtype of them and include that.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply