IF PERMISSIONS()&2=2
CREATE TABLE test_table (col1 INT)
ELSE
PRINT 'ERROR: The current user cannot create a table.'
--------
This example determines whether the current user can
grant the INSERT permission on the authors table to another user.
IF PERMISSIONS(OBJECT_ID('authors'))&0x80000=0x80000
PRINT 'INSERT on authors is grantable.'
ELSE
PRINT 'You may not GRANT INSERT permissions on authors.'
------http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_pa-pz_6f78.asp> How can I retrieve a list of objects and permissions for a specified role?
------------------------------------------------------------------------------
SELECT HAS_DBACCESS ( 'database_name' )
SELECT HAS_PERMS_BY_NAME('Adventureworks2008', 'DATABASE', 'BACKUP DATABASE');
In SQL Server 2005, you can use the Has_Perms_By_Name() function
(http://msdn2.microsoft.com/en-us/library/ms189802.aspx).
> For example, I would like to list all stored procedures which a role has
> execute permission for.
This is an example of usage:
SELECT o.SchemaAndName,
has_perms_by_name(o.SchemaAndName, 'OBJECT', 'EXECUTE')
FROM (SELECT name, SCHEMA_NAME(schema_id) AS [schema],
SCHEMA_NAME(schema_id)+'.'+name AS SchemaAndName
FROM sys.objects
WHERE type = 'P') AS o
Best Regards,Uri Dimant SQL Server MVP,
http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog:
Large scale of database and data cleansing
MS SQL Consultants:
Improves MS SQL Database Performance
