Solved

SQL Server Role Permissions

Posted on 2009-05-07
5
2,099 Views
Last Modified: 2012-08-14
Hello Experts,

I am working with permissions in SQL Server 2005/2008.  I have created a role 'TestPerm' and assigned a test user to it.  I am attempting to query the permissions that role has been granted.

I am using sp_helprotect, but dont fully understand the results: does this indicate that the user can only connect create a table? but the role cannot connect by default?    Please help
Is there another SP that would list full role permissions?

Owner      Object      Grantee      Grantor      ProtectType      Action
.      .      TestPerm      dbo      Grant           Create Table
.      .      TestUser      dbo      Grant           CONNECT
0
Comment
Question by:IClown
  • 3
  • 2
5 Comments
 
LVL 42

Expert Comment

by:EugeneZ
ID: 24330625
check
sp_helprotec
http://msdn.microsoft.com/en-us/library/ms190310(SQL.90).aspx
-----------------------------------

you probably need this script

SQL SERVER 2005 SCRIPT OUT SECURABLES AND ROLES

http://alexsdba.spaces.live.com/Blog/cns!F86565E81CD9BC16!137.entry
0
 
LVL 2

Author Comment

by:IClown
ID: 24330782
Maybe it's not the permissions....I want the user (and role to be able to create a table in an existing database).  Please let me know if what I am doing is not correct.


Use Master

GO

CREATE USER TestUser FOR LOGIN TestUser

GO

USE z_Testing

GO

Create role Role2Test

Grant Create Table to Role2Test

Grant Create Table to TestUser

sp_addrolemember 'Role2Test', 'TestUser'
 

sp_helprotect 

Open in new window

0
 
LVL 2

Author Comment

by:IClown
ID: 24330922
UPDATE:  So I can actually create it with TestUser, but it only shows up when I log back in as sa...???

Is there another permission to view the tables?
0
 
LVL 42

Accepted Solution

by:
EugeneZ earned 50 total points
ID: 24330983
view and read (select)?:


db_datareader
0
 
LVL 2

Author Closing Comment

by:IClown
ID: 31579205
well, this could take some getting used to....

Great job!  Thank you for your help
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

708 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now