Sql Script to change the Login Permission

• virgo

  Ten Centuries

  Points: 1266

  More actions

  October 1, 2010 at 12:20 am

  #143623

  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!!!:-)

• Oliiii

  SSCertifiable

  Points: 5328

  More actions

  October 1, 2010 at 5:15 am

  #1230114

  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 🙂