SQL Server 2005 User Permissions

[Walter_Ego]

I am develoing a system which cannot permit users performing adhoc queries,
how can I create a user in SQL 2005 with permissions to run stored procedures only?

Thx

[szlamany]

I am develoing a system which cannot permit users performing adhoc queries,
how can I create a user in SQL 2005 with permissions to run stored procedures only?

Thx

We do exactly that in our app's.

The USERS are granted EXEC permission to the SPROCS - that permits them only access to stored procedures, no table access without it being wrapped in a SPROC.

This also means that your UI app cannot perform adhoc queries - but everything done through a stored procedure.

We like it that way here.

[Walter_Ego]

Must I grant permission for each procedure?
(Theres quite a few in here)

[szlamany]

We script all our stored procedures in TEXT files - see post #5 in this thread:

http://www.vbforums.com/showthread.p...ight=fundsuser

With that done, then every script grant's it's own access - it's one of our SQL RULES TO LIVE BY.

It would also be very easy to loop through all the SPROCS and UDF's in the object table and grant permissions as well. I've got a sample of code for that at the office if you would consider writing a script to loop through those objects.

[Walter_Ego]

We also create all of our procs in text files, but dont do the access grants there, I do think it is a good idea and will consult it with the other developers.
(It'd also be nice to have a look at that loop script)

[Walter_Ego]

I already done it, is this OK?

DECLARE MY_CURSOR Cursor
FOR 
Select name from sysobjects where xtype = 'P'
Open My_Cursor 
DECLARE @ProcName VARCHAR(60),
            @Command VARCHAR(100)
Fetch NEXT FROM MY_Cursor INTO @ProcName
While (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @Command = 'GRANT EXECUTE ON ' + @ProcName + 'TO MyUser' 
EXEC(@Command)
FETCH NEXT FROM MY_CURSOR INTO @ProcName
END
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
GO

[kaffenils]

The USERS are granted EXEC permission to the SPROCS - that permits them only access to stored procedures, no table access without it being wrapped in a SPROC.

Just keep in mind that if a SPROC accesses objects in other databases, then the user will need explicit access to those objects.

[szlamany]

Just keep in mind that if a SPROC accesses objects in other databases, then the user will need explicit access to those objects.

But that is specifically other databases - right??

[kaffenils]

But that is specifically other databases - right??

Yes.

[kaffenils]

But that is specifically other databases - right??

To clarify a little bit more:
If a view, function or sproc in DB_1 accesses an object (that be a table,view, sproc...) in DB_2, then the user in DB_1 will need appropriate access the this object in DB_2.

That means that if you have a sproc called SPROC_1 in DB_1 that reads data from a table named TABLE_2 in DB_2, the user that exeutes SPROC_1 will need permission to execute this sproc and SELECT from TABLE_2 in DB_2. If there is a sproc called SPROC_2 in DB_2 that selects from TABLE_2 in DB_2, and SPROC_1 in DB_1 executes SPROC_2 in DB_2, then the user will need execute permission on SPROC_1 in DB_1 and SPROC_2 in DB_2. The user no longer need SELECT access to TABLE_2 in DB_2.

[szlamany]

That makes complete sense - thanks for filling my greymatter further

[Walter_Ego]

We plan to use Application Roles, yes.
I can mold this script to use a Role instead of a User correct?

[szlamany]

We plan to use Application Roles, yes.
I can mold this script to use a Role instead of a User correct?

We usually have just one ROLE in our DB - like StufilesUser or FundUser or AcctfilesUser.

We do:

GRANT EXECUTE ON frmExceptions_Inquire TO FundsUser

for example - giving the FundsUser ROLE EXECUTE to a SPROC (frmExceptions_Inquire in the example).

Then it's up to the customer to chose to either put each user into SQL security and associate them with the ROLE, or to create WINDOWS GROUPS, associate the ROLE with the GROUP and then assign user to the GROUP.

[edit] - Although ours are DATABASE ROLES not APPLICATION ROLES - never used APPLICATION ROLES (they require password to turn on - right?)

[Walter_Ego]

Hi there again - update
Am testing this now, created a SQL Login called tstLogin
Created a user in this database called tstUser
tstLogin is mapped to tstUser

tstUser is in the tstRole group
tstRole group has execute permission on the GetRecords SPROC

When I login as tstLogin, and execute the GetRecords SPROC, I get the 'SELECT permission denied' error on the table being queried from the SPROC. What am I doing wrong?

[szlamany]

tstLogin is mapped to tstUser?

What does that mean?

[Walter_Ego]

I was asked for the Login Name when I created tstUser.
Now tstUser shows in the tstLogin properties under the 'User Mapping' tab.

[szlamany]

Ok - first of all I've never used SQL 2005 Management studio - so I'm just getting into it now on my laptop - we've got one SQL 2005 instance running on a server here at the office...

BTW - why are you using a SQL LOGIN and not a WINDOWS LOGIN? Do you really want to ask the user for a username and password when they get into the app?

ok - I went to the SECURITY branch - opened up LOGINS - right clicked and selected NEW LOGIN...

What did you do at that point?

[Walter_Ego]

BTW - why are you using a SQL LOGIN and not a WINDOWS LOGIN? Do you really want to ask the user for a username and password when they get into the app?
ok - I went to the SECURITY branch - opened up LOGINS - right clicked and selected NEW LOGIN...
What did you do at that point?

After that go to one of the databases and open up the security>Users Branch and create a new user. It asks you for a login. If you go to the properties of the login itself and click on the 'User Mappings' tab you will see this user.

We need to use SQL Server security logins because not all of the users will belong in the same domain.

Guys, its been solved using the EXECUTE AS OWNER clause:

CREATE PROCEDURE HRGetRecords 
@Dept int
WITH EXECUTE AS OWNER
AS
SELECT ... ... ...

Thanks for all the help!

[kaffenils]

Guys, its been solved using the EXECUTE AS OWNER clause

Nice. Thats a new SQL Server 2005 feature.

[szlamany]

btw - there is a link in my signature on how to create "non-domain" windows authenticaton access to servers...

You do not have to be in the domain to use WINDOWS AUTHENTICAION - if you have WINDOWS XP PRO as the operating system.

[szlamany]

Nice. Thats a new SQL Server 2005 feature.

I just looked in my MSDN library and I can see how EXECUTE AS changes the "login credentials" of the user - but that would seem to me that it fixed the symptom and not the problem...

I'm certainly not looking forward to adding EXECUTE AS statements to over a 1000 SPROCS to make SQL 2005 work