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?

Hello,

I maybe wrong here, but if you look at the account that the person logons to the server with you should have all the info you need. i.e. if the logon section of the connection lists the "UID=sa" then they will have all the function available to the user "sa". So if you could possible setup differnt accounts on the server for diferent function and then in your connection section just use a differnt login name.

Hope this helps

[RIVES]

Have you ever looked at the sysobjects?...syspermissions?, sysusers? One thing common with them is the id. This id refers to the SQL server numeric id for that object.

e.g.

in the northwind db,

213575799 is the id of the customers table.


You can start from there... hope this helps.

[Nathan]

Do you know which columns in the syspermissions table will let me know what kind of permissions that user has to the object?

[RIVES]

Ok. ok.

I just wanted you to see the structure of the SQL server and how things are stored...

use the PERMISSION function... it returns an int whether or not it has permission or not.

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

e.g.

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

The return value is different depending on the paramters you passed and if you are in the Master database or not.

specify the column so that you could know up to column level.

Delete is upto object level only.

Master database has the permission to Create table...procedure etc.

[Nathan]

Just wondering... Is there a way to do this without using a stored procedure?

[RIVES]

The Permission is a stored procedure itself. You could handle the results in the front end.

Good Luck.

[Nathan]

sorry to continue bothering you... but could you give a brief example? I'm probably just a bit slow today but I'm having trouble figuring this out... thanx.