Sql Script to change the Login Permission

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Sql Script to change the Login Permission

Sql Script to change the Login Permission

Rate Topic

Display Mode

Topic Options

Author

Message

virgo

Posted Friday, October 01, 2010 12:20 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, May 18, 2013 3:54 AM
Points: 51, Visits: 185

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!!! Smile

Post #996448

Oliiii

Posted Friday, October 01, 2010 5:15 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, May 20, 2013 11:38 PM
Points: 194, Visits: 685

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 »

Permissions