Checking permissions of objects

Question

Checking permissions of objects

Sridhar-137443

Hall of Fame

Points: 3762
More actions
October 26, 2004 at 7:18 am

#109505

Hi,
How can I check Permissions of objects in a database? for example if I want to see what all permissions do I have on the database Northwind or a table Orders in the Northwind database How can I check that? I am not a database administrator. I want to see what all permissions do I have on a particular object.
Thanks,
Sridhar!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 1

Good questions. You should be able to query syspermissions and see things in there for your UID. Perhaps EM will let you see a limited view for what you have rights to.

Or run a simple select top 2 * from each table to see what comes back.

Sridhar-137443 Hall of Fame Points: 3762 More actions · Answer 2

Hi Steve,

I tried this. select top 2 * from syspermissions. I got the following values.

1 0 1 1 0 NULL NULL NULL NULL NULL NULL

2 0 1 1 0 NULL NULL NULL NULL NULL NULL

But I don't know what is my uid? how could i find that? I want to know for a particular database can I have create permissions, update permissions, insert permissions etc... But with the above information even if I know the userid also I dont have the information what I needed.

Thanks,

Sridhar!!

acedba SSChasing Mays Points: 617 More actions · Answer 3

You need to join sysusers, syspermissions, sysprotects and sysobjects tables in order to see complete info. Replace the name column in sysusers with your user name.

You can only have create permissions if you're a member of dbo or ddladmin.

Scott Reynolds SSC Enthusiast Points: 123 More actions · Answer 4

SELECT u.Name as ,

o.Name as [object],

CASE p.Action

WHEN 26 THEN 'REFERENCES'

WHEN 178 THEN 'CREATE FUNCTION'

WHEN 193 THEN 'SELECT'

WHEN 195 THEN 'INSERT'

WHEN 196 THEN 'DELETE'

WHEN 197 THEN 'UPDATE'

WHEN 198 THEN 'CREATE TABLE'

WHEN 203 THEN 'CREATE DATABASE'

WHEN 207 THEN 'CREATE VIEW'

WHEN 222 THEN 'CREATE PROCEDURE'

WHEN 224 THEN 'EXECUTE'

WHEN 228 THEN 'BACKUP DATABASE'

WHEN 233 THEN 'CREATE DEFAULT'

WHEN 235 THEN 'BACKUP LOG'

WHEN 236 THEN 'CREATE RULE'

END [privledge],

CASE p.protectType

WHEN 204 THEN 'GRANT_W_GRANT'

WHEN 205 THEN 'GRANT'

WHEN 206 THEN 'REVOKE'

END [protectType],

CASE xtype

WHEN 'U' THEN 'TABLE'

WHEN 'P' THEN 'PROCEDURE'

WHEN 'FN' THEN 'FUNCTION'

WHEN 'V' THEN 'VIEW'

END [objectType]

FROM sysprotects p

INNER JOIN sysobjects o ON p.ID = o.ID

INNER JOIN sysusers u ON p.UID = u.UID

stuartb SSC Veteran Points: 268 More actions · Answer 5

Try sp_helprotect

eg

sp_helprotect null,'UserName'

I don't believe you need special permission to run this command.

Got to love the old sybase sprocs.