?
Solved

Permissions to Databases in SSMS

Posted on 2011-09-15
11
Medium Priority
?
25 Views
Last Modified: 2016-06-20
How does the user access to all the databases from Master Database they have permissions to in Sql server Management Studio?
0
Comment
Question by:catchup99
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 10

Expert Comment

by:pramodsk40
ID: 36546030
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
 

Author Comment

by:catchup99
ID: 36546165
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
 
LVL 5

Expert Comment

by:25112
ID: 36546789
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:catchup99
ID: 36549561
The above query doesn't give which user has permissions to table. Please help me with that.
0
 
LVL 5

Accepted Solution

by:
25112 earned 500 total points
ID: 36551508
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
 
LVL 5

Assisted Solution

by:25112
25112 earned 500 total points
ID: 36551559
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
 

Author Comment

by:catchup99
ID: 36560134
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
 
LVL 5

Expert Comment

by:25112
ID: 36560722
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
 

Author Comment

by:catchup99
ID: 36560733
Thanks again for your help..
0
 
LVL 9

Expert Comment

by:Moussa Mokhtari
ID: 41661766
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question