Scripting GRANT permission to role -- sql server programming

Tibor Karaszi
2/18/2007 12:00:00 AM

Is below what you are looking for?

create database x
go
use x
create role myrole
go
create schema myschema
go
GRANT SELECT ON SCHEMA::myschema TO myrole

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

[quoted text, click to view]

brett
2/18/2007 12:17:13 PM

Thanks. This is what I was after:

GRANT SELECT ON SCHEMA::myschema TO myrole

It worked flawlessly. I wondered about the differences of using
SELECT over EXECUTE. Than I realized my question was to hasty. I
actually want to grant EXECUTE on specific stored procedures and not
allow user access to tables. So now I'm using:

grant execute on SomeSchema.someSproc to someRole

Each time I create a new sproc, I'll need to grant execute access to a
list of roles (there are currently four roles that need this). I'll
have to exec four GRANT statements for every new sproc. So, somewhere
I need to keep a running list of these statements, especially for
production servers. Is there an easy way to manage this besides just
manually adding new statements to a list (sproc) and running it
evertime?

I guess regranting is fine since the list will run everything when a
new sproc needs permissions.

Thanks,
Brett

brett
2/18/2007 1:14:32 PM

This would be nice but doesn't work since the roles come out as
strings in the GRANT statement below.

create table #roleList (roleName varchar(50))
go
insert into #roleList (roleName)
values('aspnet_Membership_BasicAccess')
insert into #roleList (roleName)
values('aspnet_Personalization_BasicAccess')
insert into #roleList (roleName)
values('aspnet_Profile_BasicAccess')
insert into #roleList (roleName)
values('aspnet_Roles_BasicAccess')
go

declare curRoles INSENSITIVE cursor
for select roleName from #roleList
declare @roleName_ varchar(50)

open curRoles
fetch next from curRoles into @roleName_
while @@Fetch_Status = 0
begin
grant execute on SomeSchema.SomeSproc to @roleName_
fetch next from curRoles into @roleName_
end
close curRoles
deallocate curRoles
go
drop table #roleList
go

brett
2/18/2007 4:15:02 PM

[quoted text, click to view]

I guess you are saying EXECUTE can't be granted on tables and the only
thing that will run here are the sprocs?

Thanks.

Erland Sommarskog
2/18/2007 11:37:34 PM

brett (account@cygen.com) writes:
[quoted text, click to view]

No you don't. Just say:

GRANT EXECUTE ON SCHEMA::SomeSchema TO somerole

and you are done, until you add a new schema.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Tibor Karaszi
2/19/2007 12:00:00 AM

[quoted text, click to view]

No, you can't grant EXECUTE on a table, and you can't grant SELECT on a procedure. It is the other
way around. But, as Erland suggested, it is simple:

Grant EXECUTE on the schema to the role.
Create the procedures in the desired schema.

Or, are you saying that you don't use stored procedures? Or is it something else with above that
won't work for you?

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

[quoted text, click to view]

brett
2/19/2007 8:46:55 AM

On Feb 19, 5:44 am, "Tibor Karaszi"
[quoted text, click to view]

No, it is all working. There are some cases where I want to grant
permission for a particular sproc on a particular shema of which I do

grant execute on object::schemname.spoc to somerole

Thanks,
Brett

Erland Sommarskog
2/19/2007 11:36:56 PM

brett (account@cygen.com) writes:
[quoted text, click to view]

That and functons.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Scripting GRANT permission to role