开发者

Transfer permissions from one domain to another in SQL Server

开发者 https://www.devze.com 2022-12-21 10:31 出处:网络
At the bottom of most of our stored procedures we have a grant similar to GRANT EXECUTE ON [dbo].[uspFOO] TO开发者_C百科 [DOMAIN\\SQLServerUsers]

At the bottom of most of our stored procedures we have a grant similar to

GRANT EXECUTE ON [dbo].[uspFOO] TO开发者_C百科 [DOMAIN\SQLServerUsers]

Luckily for me, our domain is changing and we now need to go through and change the permissions. Does anyone know of an easy way to do this using the DB metadata so I can pull out all the places where [DOMAIN\SQLServerUsers] is given permission to run and substitute it with [DOMAIN2\SQLServerUsers]?

Thanks.

For those asking, this is on SQL Server 2005.


What version of SQL Server are you on??

In 2005 and up, you could

  • create a new database role "db_executor" and do

    GRANT EXECUTE TO db_executor
    
  • grant that database role to all necessary users

This will create a "catch all" role that has execute rights on every existing and future (!!) stored proc in your database. Yes, that does include future stored procs, too! Very handy indeed (at least as long as every user is allowed to execute all stored procs)

That way, you don't have to create separate GRANT EXECUTE statements for each and every stored proc.......

0

精彩评论

暂无评论...
验证码 换一张
取 消