ansaurus

Question

Transfer permissions from one domain to another in SQL Server

Answer 1

+1 A:

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.......

marc_s 2010-02-25 15:55:47

We may go this way to ease our pain, but I know that we have specific stored procedures which we don't want users to have access to. If I understand you correctly, then this will give execute on all stored procs to the AD group which is not exactly what we want.

Rebthor 2010-02-25 16:17:09

Not to an AD group - to a SQL database role. This role can be assigned to SQL Server logins as needed and as you want

marc_s 2010-02-25 17:04:45

Ok, but if I have a stored proc named restricted_proc and have the above executor role created, can executor run restricted_proc or not? If yes, then haven't I opened a huge security hole when I assign my big AD group that role?Nonetheless, I'm marking this as accepted since it will get us up and running again quickly while we migrate permissions.

Rebthor 2010-02-25 19:10:46

Yes, sure - the db role "executor" can run *any* stored proc. And if you assign that db executor role to a big AD group, of course, all those users can run the stored procs.

marc_s 2010-02-25 21:34:04

ansaurus

tags:

views:

answers:

Transfer permissions from one domain to another in SQL Server

related questions