SQL Server

[Nathan]

I need to be able to check an open connection to see what kind of permissions the user logged onto the database (SQL Server) has. I want to be able to check to see if the person logged onto the database has read only or read and write permissions and also delete permissions so that I can disable/enable the appropriate functions in my app. Does anyone know how I could do this?

You can use the TSQL 'PERMISSIONS' function to track permissions.
It will tell you all the security (read, write, delete, create etc...)

[Nathan]

thanx for the quick reply....

I've never heard of this one so I've got a question. Can anyone who is connected to the database run this function? Can you explain this function a little further?

thanx a lot!

From the help it seems that anyone can execute this on the connection they have open to the database.
It returns quite a nasty data structure to get the data from, so you will need the help for the function. If you do not have access to the TSQL help file then....
The examples at the bottom are the most help

J.
---------------------------
(From T-SQL Help, sorry about formatting!!)

Returns a value containing a bitmap that indicates the statement, object, or column permissions for the current user.
Syntax

PERMISSIONS([objectid [, 'column']])

Arguments

objectid

Is the ID of an object. If objectid is not specified, the bitmap value contains statement permissions for the current user; otherwise, the bitmap contains object permissions on the object ID for the current user. The object specified must be in the current database. Use the OBJECT_ID function with an object name to determine the objectid value.

'column'

Is the optional name of a column for which permission information is being returned. The column must be a valid column name in the table specified by objectid.

Return Types

int

Remarks

PERMISSIONS can be used to determine whether the current user has the necessary permissions to execute a statement or to GRANT a permission on an object to another user.
The permissions information returned is a 32-bit bitmap.
The lower 16 bits reflect permissions granted to the security account for the current user, as well as permissions applied to Microsoft® Windows NT® groups or Microsoft SQL Server™ roles of which the current user is a member. For example, a returned value of 66 (hex value 0x42), when no objectid is specified, indicates the current user has permissions to execute the CREATE TABLE (decimal value 2) and BACKUP DATABASE (decimal value 64) statement permissions.

The upper 16 bits reflect the permissions that the current user can GRANT to other users. The upper 16 bits are interpreted exactly as those for the lower 16 bits described in the following tables, except they are shifted to the left by 16 bits (multiplied by 65536). For example, 0x8 (decimal value 8) is the bit indicating INSERT permissions when an objectid is specified. Whereas 0x80000 (decimal value 524288) indicates the ability to GRANT INSERT permissions, because 524288 = 8 x 65536. Due to membership in roles, it is possible to not have a permission to execute a statement, but still be able to grant that permission to someone else.

The table shows the bits used for statement permissions (objectid is not specified).

Bit (dec) Bit (hex) Statement permission
1 0x1 CREATE DATABASE (master database only)
2 0x2 CREATE TABLE
4 0x4 CREATE PROCEDURE
8 0x8 CREATE VIEW
16 0x10 CREATE RULE
32 0x20 CREATE DEFAULT
64 0x40 BACKUP DATABASE
128 0x80 BACKUP LOG
256 0x100 Reserved
The table shows the bits used for object permissions that are returned when only objectid is specified.

Bit (dec) Bit (hex) Statement permission
1 0x1 SELECT ALL
2 0x2 UPDATE ALL
4 0x4 REFERENCES ALL
8 0x8 INSERT
16 0x10 DELETE
32 0x20 EXECUTE (procedures only)
4096 0x1000 SELECT ANY (at least one column)
8192 0x2000 UPDATE ANY
16384 0x4000 REFERENCES ANY
The table shows the bits used for column-level object permissions that are returned when both objectid and column are specified.

Bit (dec) Bit (hex) Statement permission
1 0x1 SELECT
2 0x2 UPDATE
4 0x4 REFERENCES
A NULL is returned if a specified parameter is NULL or invalid (for example, an objectid or column that does not exist). The bit values for permissions that do not apply (for example EXECUTE permissions, bit 0x20, for a table) are undefined.
Use the bitwise AND (&) operator to determine each bit set in the bitmap returned by the PERMISSIONS function.
The sp_helprotect system stored procedure can also be used to return a list of object permissions for a user in the current database.

Examples
A. Use PERMISSIONS function with statement permissions

This example determines whether the current user can execute the CREATE TABLE statement.

IF PERMISSIONS()&2=2

CREATE TABLE test_table (col1 INT)
ELSE
PRINT 'ERROR: The current user cannot create a table.'


B. Use PERMISSIONS function with object permissions

This example determines whether the current user can insert a row of data into the authors table.

IF PERMISSIONS(OBJECT_ID('authors'))&8=8

PRINT 'The current user can insert data into authors.'
ELSE
PRINT 'ERROR: The current user cannot insert data into authors.'


C. Use PERMISSIONS function with grantable permissions

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.'

[Nathan]

thanx, I'll see if i can get this working...