November 22, 2015 at 3:59 pm
Hi
I have an issue with database permission .
I created a user let’s say “app_user”
After that I run this script :
Use master
GRANT SELECT ON [sys].[sysaltfiles] TO <app_user>
But I still get this error :
The SELECT permission was denied on the object 'sysaltfiles', database 'mssqlsystemresource', schema 'sys'.
Any help are much appreciated .
Thank you
November 22, 2015 at 8:24 pm
--USE DatabaseInQuestion
SELECT
'ALTER ROLE '
+ QUOTENAME(drp.[name])
+ ' ADD MEMBER '
+ QUOTENAME(dmp.[name]) COLLATE DATABASE_DEFAULT
+ ';'
FROM sys.database_role_members drm
JOIN sys.database_principals drp
ON drm.[role_principal_id] = drp.[principal_id]
JOIN sys.database_principals dmp
ON drm.[member_principal_id] = dmp.[principal_id]
;
SELECT
CASE
WHEN dperm.[state] <> 'W'
THEN dperm.[state_desc] + ' '
ELSE 'GRANT '
END
+ dperm.[permission_name]
+ ' TO ' + QUOTENAME(dgp.[name]) COLLATE DATABASE_DEFAULT
+
CASE
WHEN dperm.[state] <> 'W'
THEN ''
ELSE ' WITH GRANT OPTION'
END
+ ';'
FROM sys.database_permissions dperm
JOIN sys.database_principals dgp
ON dperm.[grantee_principal_id] = dgp.[principal_id]
WHERE dperm.[major_id] = 0
;
SELECT
CASE
WHEN dperm.[state] <> 'W'
THEN dperm.[state_desc] + ' '
ELSE 'GRANT '
END
+ dperm.[permission_name]
+ ' ON ' + QUOTENAME(OBJECT_SCHEMA_NAME(o.object_id)) + '.' + QUOTENAME(o.[name])
+
CASE
WHEN c.[column_id] IS NULL
THEN ''
ELSE '(' + QUOTENAME(c.[name]) + ')'
END
+ ' TO ' + QUOTENAME(dgp.[name]) COLLATE database_default
+
CASE
WHEN dperm.[state] <> 'W'
THEN ''
ELSE ' WITH GRANT OPTION'
END
+ ';'
FROM sys.database_permissions dperm
JOIN sys.objects o
ON dperm.[major_id] = o.[object_id]
JOIN sys.database_principals dgp
ON dperm.[grantee_principal_id] = dgp.[principal_id]
LEFT JOIN sys.columns c
ON c.[column_id] = dperm.[minor_id]
AND c.[object_id] = dperm.[major_id]
WHERE o.[is_ms_shipped] = 0
;
If run in SSMS, above is best run in Text-Mode (not Grid-Mode, the default) - <Ctrl>+T switches results pane to text mode. Save results as a .sql file (or a .rpt) off of the SQL Server box - preferably in your disaster recovery folder. Search results for "DENY".
Use XP_LOGININFO to obtain AD Group memberships, or ask your Domain Admin, or have user run WHOAMI.EXE/GROUPS from cmd.exe, or I believe user can use Windows GUI to check AD group memberships.
DENY overrides all permissions (a security flaw would exist if DENY didn't always deny). Usually there is neither a need to explicitly DENY, nor a need to use the built-in %deny% database roles. Instead of explicitly denying, it invariably is better to not GRANT. If you must deny a few members in your current AD groups or database roles (but not other members in your current AD groups or database roles), you must create a new AD group or database role, add members you wish to be explicitly denied (to the new AD group or database role), and deny the new group or database role (and not DENY your current AD groups or database roles).
If using AD groups, usually it is best to have your domain admin create AD groups that are exclusively used for database and SQL Server access. If membership is designed correctly, you will never have to DENY (you will only need to not GRANT). You can modify above script into DROP, which is useful if you are restoring a backup from one server to another, such as from a development server to a production server.
You can also modify above script to WHERE permission_name = 'DENY' or WHERE drp.[name] LIKE '%deny%', but that is less useful (longer term) and risks an oversight by omission. You can also exclude a dgp.[name], such as 'dbo', but that also risks an oversight by omission.
November 23, 2015 at 2:09 am
sysaltfiles is a system table, it's not accessible. What exactly do you want the user to be able to do?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 23, 2015 at 6:19 am
Sysaltfiles is also a SQL 2000 deprecated table included only for backward compatibility. If you answer Gail's question, we may be able to assist you with an alternative.
November 26, 2015 at 9:23 pm
Hi ..
Thank you very much on your feedback . I have asked this question to my co-worker but no feedback so far . I will get back to you once I get the info
Cheers
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply