Stored procedure excute permission issue

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

SQL Server 2008 Forums

SQL Server Administration (2008)

Stored procedure excute permission issue

Author

Topic

sainath_mannem
Starting Member

6 Posts

Posted - 2011-09-30 : 09:45:42

Hi,
I am new to SQL Server,
I have two databases A and B in my database server.
My requirement is to give execute permission on
one of the procedure(proc1) in database A to database B user(UserB).
Is it possible in SQL Server?
If it is possible, Can you please give the SQL Script for this...

Sainath Mannem

Sainath Mannem

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-09-30 : 11:55:18

Add user to new database and give him execute privilege on stored procedure.

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-30 : 13:52:15

Is it possible in SQL Server?

I don't think so. A user is known only in the db in which it is created. To access to a db, the login must map to one (only one) user in that db. That is the design of login-user relationship in sql server.

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2011-09-30 : 18:19:56

If a login needs access more then one databases how you will setup?

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2011-09-30 : 19:34:32

2 ways to setup

- create the user in the db, mapping it to the login

- the login -> property -> user mapping -> select db and type the user name

Sachin.Nand

2937 Posts

Posted - 2011-10-01 : 11:01:05

I believe that both users are mapped to different logins.If that's the case then you can give Impersonate rights to login for database A to that of login of database B.Something like this


Grant Impersonate on Login :: LoginA TO LoginB
GO

and then when using LoginB credentials to access database A use something like this


Execute as Login = 'LoginA';
Go
Use datbaseA
Go

Execute proc1

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources