Click here to Skip to main content
15,887,776 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more: , +
Hi All
I need to create on repository on an etl tool.For that i got to review the permissions over the database for the user sa(read and write access) I tried the following codes:-

What I have tried:

JavaScript
SELECT DATABASEPROPERTYEX('TestProfiler', 'Updateability')


Op: READ_WRITE

JavaScript
SELECT name, is_read_only
FROM sys.databases
WHERE name = 'TestProfiler'
GO


Name IsReadOnly
Op TestProfiler 0

C#
SELECT u.name, o.name
FROM syspermissions p, sysobjects o, sysusers u
WHERE p.id = o.id
AND u.uid = p.grantee
AND u.name IN ('sa')
AND o.xtype = 'U'
AND p.actadd = 27


op:Name Name
Blank
From the first query its clear that the table has got read/write access, but whether for sa user or not I dont know:( Please provide a solution to this,or an alternative way to review the read and write permission access(my login credentials involves user sa)
Thank you
Regards
Posted
Updated 28-May-16 0:05am
v3

1 solution

It's not as easy as you think it should be!

Firstly - avoid using the syspermissions table - use the sys views instead. syspermissions will be removed in later versions of SQL

There is a very popular solution on this link (solution by "Jeremy") SQL Server query to find all permissions/access for all users in a database[^] which gives a query for all objects in the database - you should be able to adapt it to your needs
 
Share this answer
 
Comments
mousau 30-May-16 10:29am    
Hi Chill60
Wow, seems so cool, Thanks a lot for the information update.I have been through the solutions provided at the site,gotta implement that.Let me check from my end,or contact the admin team perhaps.But thanks a lot again for sharing the valuable information.

Thanks
Regards

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900