Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Larry Sumuri » Database Users And Associated Database Roles
Add to briefcase
11 posts, Page 1 of 2
12»»

Database Users And Associated Database Roles Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Larry Sumuri-373443
Posted Monday, January 25, 2010 4:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:19 PM
Points: 17, Visits: 86
Comments posted to this topic are about the item Database Users And Associated Database Roles
Post #853371
Francisco-124387
Posted Tuesday, February 09, 2010 9:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 11, 2013 9:48 PM
Points: 1, Visits: 51
When I try to run this script I am geting the error below:

Msg 102, Level 15, State 1, Line 14
Incorrect syntax near ' '.

It does not make any sense and I cannot find out why this error is being displayed. Has anyone else had this issue? Thanks!
Post #862501
Larry Sumuri-373443
Posted Tuesday, February 09, 2010 3:40 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:19 PM
Points: 17, Visits: 86
The boxes '    ' representing spaces/tab, are part of the query being copied over, get rid of them and your query will execute OK.

For instance, "    SELECT role_principal_id, member_principal_id FROM sys.database_role_members"

Delete the     , it may not show in your query window, but just delete tab or space of line inside for it to look like "SELECT role_principal_id, member_principal_id FROM sys.database_role_members".

Do this for the rest of the line and you should be OK.
Post #862786
Ritesh Medhe
Posted Wednesday, February 10, 2010 4:29 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, Visits: 234
cool
Post #863083
Ritesh Medhe
Posted Wednesday, February 10, 2010 4:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, Visits: 234
just a small thing make use of table variable instaed of temp tables. or local temp table
Post #863086
Ritesh Medhe
Posted Wednesday, February 10, 2010 4:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 1:22 AM
Points: 325, Visits: 234
is it possible to drill down to object level, say particular user have xyz right on abc object. eg: user1 have execute permission on procedure1?

Thanks in advance!!!!!!
Post #863091
mstjean
Posted Wednesday, February 10, 2010 1:31 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:15 PM
Points: 423, Visits: 1,948
You don't need a cursor; run this from the db you want to collect information on.

SELECT
	sdp2.[name] as DBUser, sdp2.type_desc as DBUserType,
	sdp1.[name] as DBUserRole, sdp1.type_desc as DBUserRoleType
FROM sys.database_role_members drm
LEFT JOIN sys.database_principals sdp1 on drm.role_principal_id=sdp1.principal_id
LEFT JOIN sys.database_principals sdp2 on drm.member_principal_id=sdp2.principal_id
ORDER BY
	sdp2.name,sdp1.name

Cursors are useful if you don't know SQL
Post #863605
Larry Sumuri-373443
Posted Wednesday, February 10, 2010 3:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, January 23, 2013 5:19 PM
Points: 17, Visits: 86
SSC-Enthusiastic, I like the script you posted, it works the same, and importantly I do not have to user a cursor and create a temp table to get the information I want.

Thanks.
Post #863690
mstjean
Posted Wednesday, February 10, 2010 3:56 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 1:15 PM
Points: 423, Visits: 1,948
Glad you like it.

By the way, my identity is not "SSC-Enthusiastic", just as yours is not "Forum Newbie".

Generally you should try to avoid using cursors in tsql-- with few exceptions. There are some real gurus on this site, it's a great resource.

Cursors are useful if you don't know SQL
Post #863701
YSLGuru
Posted Wednesday, February 17, 2010 2:57 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 11:18 AM
Points: 848, Visits: 1,453
mstjean (2/10/2010)
Glad you like it.

By the way, my identity is not "SSC-Enthusiastic", just as yours is not "Forum Newbie".

Generally you should try to avoid using cursors in tsql-- with few exceptions. There are some real gurus on this site, it's a great resource.




DITTO to the avoiding Cursors and Guru comment to.

When I first looked at this I was like thats cool but whats with the CURSOR? I assumed there must be some klind of restriction disallowing it.

Larry - Is your background in programming verses DB related?

Kindest Regards,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.
Post #867549
« Prev Topic | Next Topic »

Add to briefcase
11 posts, Page 1 of 2
12»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.