Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sql Script to change the Login Permission Expand / Collapse
Author
Message
Posted Friday, October 1, 2010 12:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 5:26 AM
Points: 56, Visits: 336
Hi Guys,

I need a help on SQL Script and not a manual procedure (as i know on it).to do below scenario


1) A login 'testuser' exists on a server which as db_dataread,db_datawrite and db_owner permissions and have around 4 Users tagged to it under 3 different Databases

Datasae1 with user testuser tagged to login testuser
Datasae2 with user testuser tagged to login testuser
Datasae3 with user testuser tagged to login testuser


So now I need a script to

1) Change the permission on login 'testuser' to just db_datareader, db_datawriter, db_ddladmin and execute.


2) After change in permissions on login 'testuser' i hope corresponding users tagged to it on different DBs will also get updated , pls confirm?

Thanks in advance!!!
Post #996448
Posted Friday, October 1, 2010 5:15 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:21 AM
Points: 206, Visits: 764
Try this on each dbs:
USE [Datasae1]
GO
EXEC sp_addrolemember N'db_ddladmin', N'testuser'
EXEC sp_droprolemember N'db_owner', N'testuser'
GRANT EXECUTE TO testuser

You have to do it on each DB since you handle the DB security at the DB level :)
Post #996544
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse