execute permissions to the user

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

execute permissions to the user

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-11-27 : 07:37:14

Dear All..
i've given permissions to a particular user executing the procedure permissions like "grant exec to 'user_name'"

i dont know exactly is it works at database level or server level. and at the roles, i didnot find the execute sp role. so please guide me how can i achieve this.....

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 07:40:46

It works at database level. There is no execute sp role in SQL Server. Are you trying to give execute permission on all Sps for that user?

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-11-27 : 08:02:09

yes...
exactly....
please let me know how can i do this

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 08:19:53

SELECT 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO [Username]' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0

and paste output.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-11-27 : 10:08:48

It's usually a good idea to create a role and then assign the execute permissions to the role. That saves you from repeatedly granting the same rights to multiple users. You just make them all members of the role instead.

--
Gail Shaw
SQL Server MVP

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 10:54:59

See db_executor role in SQL 2005. But it will give execute on functions as well. Careful!

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-11-27 : 11:40:28

Are you sure that's a built-in role? I can't find any mention of it in BoL and it's not in any of my SQL 2005 or 2008 databases.

select name from master.sys.database_principals where type_desc = 'Database_Role' and is_fixed_role = 1

db_owner
db_accessadmin
db_securityadmin
db_ddladmin
db_backupoperator
db_datareader
db_datawriter
db_denydatareader
db_denydatawriter

--
Gail Shaw
SQL Server MVP

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 11:41:38

That is not built-in role. You can add it in SQL 2005.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-11-27 : 11:49:20

Ah, sorry, my mistake. I thought you were referring to a built-in role.
Was worried that I'd missed something significant there for a moment.

--
Gail Shaw
SQL Server MVP

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-27 : 11:57:46

No problem

. We used it for Stored procs but it gives execute permissions on functions as well so we ignored it.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-11-28 : 00:30:02

Sodeep it is really fantastic....
excellent DBA you are....

thank you gilamaster for the timely support....

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-28 : 12:41:57

Thanks!!!

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-12-01 : 02:59:28

How can i provide the same user to check the jobs success and failure?

while connecting with this user, sql server agent is not displaying in object explorer.

Arnav
Even you learn 1%, Learn it with 100% confidence.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-01 : 06:47:50

Books Online. Section titled "SQL Server Agent Fixed Database Roles"
Or, if you prefer: http://msdn.microsoft.com/en-us/library/ms188283(SQL.90).aspx

That's assuming you're on 2005, that it. I seem to recall that in SQL 2000 it was a lot more complex, possibly even requiring sysadmin. Not sure there.

--
Gail Shaw
SQL Server MVP

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-12-01 : 06:51:37

I'm working with 2005

Arnav
Even you learn 1%, Learn it with 100% confidence.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-12-01 : 08:33:08

Then that link should give you all the info you need.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources