[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL Server Role Permissions

Posted on 2009-05-07
5
Medium Priority
?
2,129 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 43

Expert Comment

by:Eugene Z
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 43

Accepted Solution

by:
Eugene Z earned 200 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

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses
Course of the Month8 days, 10 hours left to enroll

613 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