[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 30
  • Last Modified:

Permissions to Databases in SSMS

How does the user access to all the databases from Master Database they have permissions to in Sql server Management Studio?
0
catchup99
Asked:
catchup99
2 Solutions
 
pramodsk40Commented:
you can have the user as sysadmin role which will have access to all databases. Or you want access other databases from Master db ?
0
 
catchup99Author Commented:
I think im not clear with my question. I need a c# code to show all tables that i have permission to in Sql server Management studio
0
 
25112Commented:
this is the SQL query that will tell you that.. can you plug this into C#
select name,
CanSelectAll = case when Permissions(id)&1 = 1 then 'Yes' else 'No' END ,
CanSelectAny = case when Permissions(id)&4096 = 4096 then 'Yes' else 'No' END ,
CanUpdateAll = case when Permissions(id)&2 = 2 then 'Yes' else 'No' END ,
CanUpdateAny = case when Permissions(id)&8192 = 8192 then 'Yes' else 'No' END ,
CanInsert = case when Permissions(id)&8 = 8 then 'Yes' else 'No' END ,
CanDelete = case when Permissions(id)&16 = 16 then 'Yes' else 'No' END , 
CanExecute = case when Permissions(id)&32 = 32 then 'Yes' else 'No' END
from sysobjects where type = 'U';

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
catchup99Author Commented:
The above query doesn't give which user has permissions to table. Please help me with that.
0
 
25112Commented:
the above query will tell you what permission you have.. if you want to see for another, try this syntax..  (put/replace your userid in the first line)
execute as user = 'testIDNet';
select name,
CanSelectAll = case when Permissions(id)&1 = 1 then 'Yes' else 'No' END ,
CanSelectAny = case when Permissions(id)&4096 = 4096 then 'Yes' else 'No' END ,
CanUpdateAll = case when Permissions(id)&2 = 2 then 'Yes' else 'No' END ,
CanUpdateAny = case when Permissions(id)&8192 = 8192 then 'Yes' else 'No' END ,
CanInsert = case when Permissions(id)&8 = 8 then 'Yes' else 'No' END ,
CanDelete = case when Permissions(id)&16 = 16 then 'Yes' else 'No' END , 
CanExecute = case when Permissions(id)&32 = 32 then 'Yes' else 'No' END
from sysobjects where type = 'P';
revert;

Open in new window

0
 
25112Commented:
if you want to see it for all users.. then filter it as needed..
CREATE TABLE #permissions (ObjectOwner sysname, ObjectName sysname, Grantee sysname, Grantor sysname, protecttype sysname, Privilege sysname, [column] varchar(1000))
DECLARE @DBName sysname
SET @DBName = db_name()	
INSERT #permissions EXEC ('EXEC ' + @DBName + '.dbo.sp_helprotect')

SELECT Grantee , UPPER(ProtectType) ,UPPER(Privilege) ,ObjectName
FROM #permissions, sysobjects WHERE objectname = name 

DROP TABLE #permissions

Open in new window

0
 
catchup99Author Commented:
Thanks for your response...Now i need to write an application in c# so that when the user login with their ID should display tables from sql that has access to and when clicked on particular table data should be displayed.
Any ideas?
0
 
25112Commented:
catchup99, glad it worked for you.

This is the SQL Server Zone.. I would recommend posting the C# question in that zone for better responses. Since this is SQL zone, you are going to get help with SQL code not C# (as much)
0
 
catchup99Author Commented:
Thanks again for your help..
0
 
Moussa MokhtariEnterpreneurCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now