I'm maintaining an application that runs with SQL Server 8.0.760 SP3.
When I try to "see" the permissions of the current user I'm logged with, I do this:
SELECT * FROM fn_my_permissions(NULL, 'SERVER');
However, the server returns this error message:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'fn_my_permissions'.
If I try the following query: SELECT * FROM dbo.fn_my_permissions(NULL, 'SERVER');, I get the same error message.
How can I list all the current user permissions?
I had to write a permissions audit for some compliance report a while back and now I have integrated that report into my suite of administrative DB scripts.
I put a copy of the procedure that generates the report in pastebin for you and I verified that it works with SQL Server 2000 still.
It generates the report in a tree view with the login and default database listed first, then any server level roles, then any database roles and explicitly granted permissions.
Edited to add the actual code directly in the reply rather than rely on the pastebin link
CREATE PROCEDURE [dbo].[spAdmin_PermissionsReport]
AS
BEGIN
/*
Description: A report of each login in a database server along with its
corresponding server roles and the databases that the login has access to.
For each database, the database roles and explicit permissions in that
database are also listed.
The report is generated in the following format:
ServerName
LoginName [DefaultDB]
Created
Full Name
ServerRole(s):
ServerRole1
ServerRole2
...
Database(s):
db1
DatabaseRole(s):
dbr1
dbr2
...
Permissions:
object1 type
oblect2 type
...
db2
DatabaseRole(s):
dbr1
dbr2
...
Permissions:
object1 type
oblect2 type
...
...
*/
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET NOCOUNT ON
DECLARE
@SQL VARCHAR(8000)
, @DB VARCHAR(128)
, @LoginName VARCHAR(128)
, @DefaultDB VARCHAR(128)
, @CreateDate DATETIME
, @MemberSID VARBINARY(85)
, @UID SMALLINT
, @ServerRole VARCHAR(128)
, @DatabaseRole VARCHAR(128)
, @Permission VARCHAR(200)
, @FirstServerRole BIT
, @FirstDatabase BIT
, @FirstDatabaseRole BIT
, @FirstPermission BIT
, @RecordSeparator VARCHAR(1000)
-- The 'first' variables flag when permission headers should be printed, 1 means yes.
SELECT
@FirstServerRole = 1
, @FirstDatabase = 1
, @FirstDatabaseRole = 1
, @FirstPermission = 1
, @RecordSeparator = '================================================================================'
CREATE TABLE #t_Logins (
LoginName VARCHAR(128) NULL
, MemberSID VARBINARY(85) NULL
, DefaultDB VARCHAR(128) NULL
, CreateDate DATETIME NULL
)
CREATE TABLE #t_Databases (
DBName VARCHAR(128) NOT NULL
)
-- Roles for a server
CREATE TABLE #t_SRVRoleMember (
ServerRole VARCHAR(128) NOT NULL
, MemberName VARCHAR(128) NOT NULL
, MemberSID VARBINARY(85) NOT NULL
)
-- Roles for the current database
CREATE TABLE #t_RoleMember (
DBRole VARCHAR(128) NOT NULL
, MemberName VARCHAR(128) NOT NULL
, MemberSID VARBINARY(85) NULL
)
-- All database roles by database
CREATE TABLE #t_DBRoleMember (
DBName VARCHAR(128) NOT NULL
, DBRole VARCHAR(128) NOT NULL
, MemberName VARCHAR(128) NOT NULL
, MemberSID VARBINARY(85) NOT NULL
, UID SMALLINT NOT NULL
)
-- Permissions for a database
CREATE TABLE #t_Protect (
Owner VARCHAR(128) NOT NULL
, Object VARCHAR(128) NOT NULL
, Grantee VARCHAR(128) NOT NULL
, Grantor VARCHAR(128) NOT NULL
, ProtectType VARCHAR(50) NOT NULL
, [Action] VARCHAR(50) NOT NULL
, [Column] VARCHAR(50) NULL
)
-- All permissions by database
CREATE TABLE #t_DBProtect (
DBName VARCHAR(128) NOT NULL
, Owner VARCHAR(128) NOT NULL
, Object VARCHAR(128) NOT NULL
, Grantee VARCHAR(128) NOT NULL
, Grantor VARCHAR(128) NOT NULL
, ProtectType VARCHAR(50) NOT NULL
, [Action] VARCHAR(50) NOT NULL
, [Column] VARCHAR(50) NULL
, UID SMALLINT NOT NULL
, MemberSID VARBINARY(85) NOT NULL
)
-- Populate server logins
-- PRINT 'Populate Server Logins'
INSERT INTO #t_Logins
SELECT
sl.[name]
, sl.SID
, sl.DBName
, sl.CreateDate
FROM
master.dbo.SYSLOGINS sl
WHERE
[name] IS NOT NULL
AND SID IS NOT NULL
ORDER BY
[name]
-- Populate the databases
-- PRINT 'Populate Databases'
INSERT INTO #t_Databases
SELECT
d.[name]
FROM
master..sysdatabases d
WHERE
d.[name] NOT IN ( 'tempdb', 'model' )
AND HAS_DBACCESS(d.[name]) = 1
AND d.name = 'TMPSERVICE'
ORDER BY
d.[name]
-- Populate logins' server roles
-- PRINT 'Populate Logins'' Server Roles'
INSERT INTO #t_SRVRoleMember
EXEC sp_HelpSRVRoleMember
-- Populate logins' database roles
DECLARE cDBs CURSOR FOR
SELECT
DBName
FROM
#t_Databases
ORDER BY
DBName
OPEN cDBs
FETCH NEXT FROM cDBs INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get the current database role
SET @SQL = 'INSERT INTO #t_RoleMember EXEC [' + @DB + '].dbo.sp_helprolemember'
-- PRINT 'Insert DB Roles for ' + @DB
EXEC(@SQL)
SELECT @SQL = '' +
'INSERT INTO #t_DBRoleMember ( ' +
' DBName ' +
', DBRole ' +
', MemberName ' +
', MemberSID ' +
', UID ' +
') ' +
' SELECT ' +
' ''' + @DB + ''' AS DBName' +
' , ''public'' AS DBRole' +
' , tl.LoginName ' +
' , tl.MemberSID ' +
' , u.SID ' +
' FROM ' +
' #t_Logins tl ' +
' JOIN [' + @DB + '].dbo.sysusers u ' +
' ON tl.MemberSID = u.SID '
EXEC(@SQL)
-- Store the current database role by database
SELECT @SQL = '' +
'INSERT INTO #t_DBRoleMember ( ' +
' DBName ' +
', DBRole ' +
', MemberName ' +
', MemberSID ' +
', UID ' +
') ' +
' SELECT ' +
' ''' + @DB + ''' ' +
' , t.DBRole ' +
' , t.MemberName ' +
' , t.MemberSID ' +
' , u.UID ' +
' FROM ' +
' #t_RoleMember t ' +
' JOIN #t_Logins tl ' +
' ON t.MemberSID = tl.MemberSID ' +
' JOIN [' + @DB + '].dbo.sysusers u ' +
' ON t.MemberName = u.[name] COLLATE SQL_Latin1_General_CP1_CI_AS ' +
' ORDER BY ' +
' t.MemberName ' +
' , t.DBRole '
EXEC(@SQL)
-- purge out the current database roles for next database
DELETE FROM #t_RoleMember
-- Get the database permissions
SET @SQL = 'INSERT INTO #t_Protect EXEC [' + @DB + '].dbo.sp_helprotect'
-- PRINT 'Insert Permissions for ' + @DB
EXEC(@SQL)
-- Store the current database permissions by database
SELECT @SQL = '' +
'INSERT INTO #t_DBProtect ( ' +
' DBName ' +
', Owner ' +
', Object ' +
', Grantee ' +
', Grantor ' +
', ProtectType ' +
', [Action] ' +
', [Column] ' +
', UID ' +
', MemberSID ' +
') ' +
' SELECT ' +
' ''' + @DB + ''' AS DBName ' +
' , t.Owner ' +
' , t.Object ' +
' , t.Grantee ' +
' , t.Grantor ' +
' , t.ProtectType ' +
' , t.[Action] ' +
' , t.[Column] ' +
' , u.UID ' +
' , u.SID ' +
' FROM ' +
' #t_Protect t ' +
' JOIN [' + @DB + '].dbo.sysusers u ' +
' JOIN #t_Logins l ' +
' ON u.SID = l.MemberSID ' +
' ON t.Grantee = u.Name COLLATE SQL_Latin1_General_CP1_CI_AS ' +
' ORDER BY ' +
' Grantee ' +
' , Object'
EXEC(@SQL)
-- purge out the current database permissions for next database
DELETE FROM #t_Protect
FETCH NEXT FROM cDBs INTO @DB
END
CLOSE cDBs
DEALLOCATE cDBs
-- SELECT * FROM #t_Logins
-- SELECT * FROM #t_Databases
-- SELECT * FROM #t_SRVRoleMember
-- SELECT * FROM #t_DBRoleMember
-- SELECT * FROM #t_DBProtect
-- GENERATE REPORT
PRINT 'Begin report - ' + CONVERT(VARCHAR, GETDATE())
PRINT 'Report: spAdmin_PermissionsReport'
PRINT 'Description: A report of each login in a database server along with its'
PRINT 'corresponding server roles and the databases that the login has access to.'
PRINT 'For each database, the database roles and explicit permissions in that'
PRINT 'database are listed.'
PRINT @RecordSeparator
PRINT 'This report reflects local accounts only.'
PRINT @RecordSeparator
SELECT @SQL = 'SERVERNAME: ' + @@SERVERNAME
PRINT @SQL
PRINT @RecordSeparator
DECLARE cLogins CURSOR FOR
SELECT
LoginName
, MemberSID
, DefaultDB
, CreateDate
FROM
#t_Logins
ORDER BY
LoginName
OPEN cLogins
FETCH NEXT FROM cLogins INTO @LoginName, @MemberSID, @DefaultDB, @CreateDate
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = CHAR(9) + 'LOGINNAME:' + CHAR(9) + @LoginName + ' [' + @DefaultDB + ']'
PRINT @SQL
SELECT @SQL = CHAR(9) + 'CREATED:' + CHAR(9) + CONVERT(VARCHAR, @CreateDate)
PRINT @SQL
DECLARE cServerRoles CURSOR FOR
SELECT
ServerRole
FROM
#t_SRVRoleMember
WHERE
MemberSID = @MemberSID
ORDER BY
ServerRole
OPEN cServerRoles
FETCH NEXT FROM cServerRoles INTO @ServerRole
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( @FirstServerRole = 1 )
BEGIN
PRINT ''
SELECT @SQL = CHAR(9) + 'SERVERROLE(S):'
PRINT @SQL
SET @FirstServerRole = 0
END
SELECT @SQL = CHAR(9) + CHAR(9) + @ServerRole
PRINT @SQL
FETCH NEXT FROM cServerRoles INTO @ServerRole
END
CLOSE cServerRoles
DEALLOCATE cServerRoles
DECLARE cDatabases CURSOR FOR
SELECT
DBName
FROM
#t_Databases
ORDER BY
DBName
OPEN cDatabases
FETCH NEXT FROM cDatabases INTO @DB
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE cDatabaseRoles CURSOR FOR
SELECT
DBRole
FROM
#t_DBRoleMember
WHERE
DBName = @DB
AND MemberSID = @MemberSID
ORDER BY
DBRole
OPEN cDatabaseRoles
FETCH NEXT FROM cDatabaseRoles INTO @DatabaseRole
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( @FirstDatabase = 1 )
BEGIN
PRINT ''
SELECT @SQL = CHAR(9) + 'DATABASE(S):' + CHAR(9) + '[' + @DB + ']'
PRINT @SQL
SET @FirstDatabase = 0
END
IF ( @FirstDatabaseRole = 1 )
BEGIN
SELECT @SQL = CHAR(9) + CHAR(9) + 'DATABASEROLE(S):'
PRINT @SQL
SET @FirstDatabaseRole = 0
END
SELECT @SQL = CHAR(9) + CHAR(9) + CHAR(9) + @DatabaseRole
PRINT @SQL
FETCH NEXT FROM cDatabaseRoles INTO @DatabaseRole
END
CLOSE cDatabaseRoles
DEALLOCATE cDatabaseRoles
DECLARE cPermissions CURSOR FOR
SELECT
UPPER(LTRIM(RTRIM(dbp.ProtectType))) + ' ' + UPPER(LTRIM(RTRIM(dbp.[Action]))) + CASE WHEN dbp.[Column] NOT IN ('.', '(All+New)', '(All)') THEN ' (' + dbp.[Column] + ')' ELSE '' END + ' ON ' + dbp.Object + ' TO ' + dbp.Grantee
FROM
#t_DBProtect dbp
JOIN #t_Logins l
ON dbp.MemberSID = l.MemberSID
WHERE
dbp.DBName = @DB
AND l.MemberSID = @MemberSID
OPEN cPermissions
FETCH NEXT FROM cPermissions INTO @Permission
WHILE @@FETCH_STATUS = 0
BEGIN
IF ( @FirstDatabase = 1 )
BEGIN
PRINT ''
SELECT @SQL = CHAR(9) + 'DATABASE(S):' + CHAR(9) + '[' + @DB + ']'
PRINT @SQL
SET @FirstDatabase = 0
END
IF ( @FirstPermission = 1)
BEGIN
SELECT @SQL = CHAR(9) + CHAR(9) + 'PERMISSION(S):'
PRINT @SQL
SET @FirstPermission = 0
END
SELECT @SQL = CHAR(9) + CHAR(9) + CHAR(9) + @Permission
PRINT @SQL
FETCH NEXT FROM cPermissions INTO @Permission
END
CLOSE cPermissions
DEALLOCATE cPermissions
SET @FirstDatabaseRole = 1
SET @FirstDatabase = 1
SET @FirstPermission = 1
FETCH NEXT FROM cDatabases INTO @DB
END
CLOSE cDatabases
DEALLOCATE cDatabases
SET @FirstDatabase = 1
SET @FirstServerRole = 1
PRINT @RecordSeparator
FETCH NEXT FROM cLogins INTO @LoginName, @MemberSID, @DefaultDB, @CreateDate
END
PRINT 'End report - ' + CONVERT(VARCHAR, GETDATE())
CLOSE cLogins
DEALLOCATE cLogins
DROP TABLE #t_DBProtect
DROP TABLE #t_Protect
DROP TABLE #t_DBRoleMember
DROP TABLE #t_RoleMember
DROP TABLE #t_SRVRoleMember
DROP TABLE #t_Databases
DROP TABLE #t_Logins
END
Thanks to this thread: 'how to check login user permission in sql server 2000', I managed to see the current user permissions.
I run the following query:
select
o.name as [table],
--u.name as through_user,
isnull(min(CASE p1.protecttype WHEN 204 THEN 1 WHEN 205 THEN 1 WHEN 206 THEN 0 ELSE fixedroles.reader END), 0) as [select],
isnull(min(CASE p2.protecttype WHEN 204 THEN 1 WHEN 205 THEN 1 WHEN 206 THEN 0 ELSE fixedroles.writer END), 0) as [insert],
isnull(min(CASE p3.protecttype WHEN 204 THEN 1 WHEN 205 THEN 1 WHEN 206 THEN 0 ELSE fixedroles.writer END), 0) as [update],
isnull(min(CASE p4.protecttype WHEN 204 THEN 1 WHEN 205 THEN 1 WHEN 206 THEN 0 ELSE fixedroles.writer END), 0) as [delete]
from sysusers u
inner join sysobjects o on ( o.xtype in ( 'V', 'U' ) and o.Name NOT LIKE 'dt%' and o.Name NOT LIKE 'sys%' )
left join sysprotects as p1 on p1.uid = u.uid and p1.id = o.id and p1.action = 193
left join sysprotects as p2 on p2.uid = u.uid and p2.id = o.id and p2.action = 195
left join sysprotects as p3 on p3.uid = u.uid and p3.id = o.id and p3.action = 197
left join sysprotects as p4 on p4.uid = u.uid and p4.id = o.id and p4.action = 196
left join sysprotects as p5 on p5.uid = u.uid and p5.id = o.id and p5.action = 224
left join (
select
case when is_member('db_denydatareader')=1 then 0 --explicit deny
when is_member('db_datareader')=1 or is_member('db_owner')=1 then 1
end as reader, --write permissions doesn't give you permissions to read
case when is_member('db_denydatareader')=1 --explicit allow
or is_member('db_denydatawriter')=1 then 0 --explicit deny
when (is_member('db_datareader')=1 or is_member('db_owner')=1)
and (is_member('db_datawriter')=1 or is_member('db_owner')=1) then 1
else null
end as writer --you dont't have write permissions if you don't have read permissions
) fixedroles on u.uid=0
where (u.name=SYSTEM_USER
or exists (select 1 from sysusers gu where name=SYSTEM_USER and u.uid=gu.gid)
or u.uid=0 --public role
)
and (coalesce(p1.action, p2.action, p3.action, p4.action, p5.action) is not null
or coalesce(fixedroles.reader, fixedroles.writer) is not null
)
group by o.name
