Improve company productivity with a Business Account.Sign Up

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

SQL Server Role Permissions

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
IClown
Asked:
IClown
  • 3
  • 2
1 Solution
 
Eugene ZCommented:
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
 
IClownAuthor Commented:
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
 
IClownAuthor Commented:
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
 
Eugene ZCommented:
view and read (select)?:


db_datareader
0
 
IClownAuthor Commented:
well, this could take some getting used to....

Great job!  Thank you for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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