SQL Server '05: Adding New Users and setting permissions

[aconybeare]

Hi,

I have a database that has been using a user who has admin priveleges, now that the app. is moving into production I want to create a new user with cut down permissions, does anyone have any ideas on the best way of creating this user so that they can execute stored procedures etc.

Do I need to go through and execute the following for each sp? (+/-350 yikes)

 
GRANT EXECUTE ON [proc_name] TO [user]

Additionally how do I manage this going forward? Do I have to add the above after I create or drop and recreate my procedures?

USE myDB
Go
If Exists (SELECT * FROM sysobjects WHERE ID =
   OBJECT_ID('up_procname') AND sysstat & 0xf = 4)
   DROP PROCEDURE up_procname
Go
CREATE PROCEDURE up_procname(
   @Jnl_Title varchar(250) OUTPUT,
   @Jnl_Code varchar(10),
   @Status varchar(20) = 'A'
)
As
   SET NOCOUNT ON 
   SET @Jnl_Title=
   	(
   		SELECT j.Jnl_Title
		FROM Jnl_Config j
		WHERE j.Jnl_Code=@Jnl_Code
	)

   Return @@Error
   SET NOCOUNT OFF
GO
GRANT EXECUTE ON up_procname TO [myuser] 
GO

Any guidance would be appreciated

Cheers Al

[Tom Sawyer]

If you're using 2005, the easiest way is to create a schema and add the sps to that and then make the user the owner of that schema. Right now, your sp isn't called up_procname, it's called dbo.up_procname, since dbo is the default schema. To make your own, just do:

CREATE SCHEMA MyProc AUTHORIZATION [myuser]
GO

Then you have your own little section that all the stuff to do with your app can be a part of and your user acts like the dbo account for everything that's assigned to that schema. In your sps, change the definition to be:

CREATE PROCEDURE MyProc.up_procname

or, if they're already all in your db, you can just move them to the new schema:

ALTER SCHEMA MyProc
    TRANSFER dbo.up_procname

That way, you don't have to worry about setting permissions on an object by object basis, since your user will have full control of everything within the schema and no permissions for anything outside of it, so it eliminates that security risk.

[Clanguage]

CREATE SCHEMA MyProc AUTHORIZATION [myuser]
GO

Thank you Tom. Cool tip. I have a DB admin at work but maintain a DB at home and I am glad to learn about this

[aconybeare]

Tom,

Thanks for your reply.

My db is already set up in a different schema from dbo, when I log in I've set the default schema so I don't have to specify the schema for each table, SP etc.

I'd like to keep my existing set up as it is with my orginal user who can do everything and I use this user for managing the db (adding and altering my tables and SP's). I just want to add this new user, who can execute SPs but not add, alter or delete tables, SP's etc.. I guess I'm talking myself into manually granting execute for each SP.

Think I'm going to have to go on a course or something as this is a very gray area for me.

Cheers Al

[szlamany]

Seems like a lot of work to re-create the SPROCS in a different schema.

How about creating a DATABASE ROLE - that's what we do.

Then assign EXECUTE permission to that DATABASE ROLE - not some individual users.

Then you can chose to either give that DATABASE ROLE to an individual user (still a lot of work) or to give that DATABASE ROLE to a WINDOWS GROUP.

We do the later - give it to a WINDOWS GROUP. Then in AD they assign that WINDOWS GROUP to certain users. That way security maintenance really just resides in AD.

Typical script we have

Use Stufiles
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAppEle_P]') 
	and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAppEle_P]
GO

Create Procedure GetAppEle_P
as

Set NoCount On

Select AER.AppID, AER.ROleID
FROM AppElemRole_T AER
WHERE AER.AppId < 10000 and AER.RoleId<1000
ORDER BY AER.AppId DESC

Select AE.AppDesc, RN.RptName, RO.RoleFlag
FROM AppElemRole_T AER
LEFT Join AppElem_T AE on AE.AppId=AER.AppId
Left Join Role_T RO on RO.RoleId=AER.RoleId
Left Join ReportName_T RN on RN.RptSP=Left(RO.RoleFlag,CharIndex('~',RoleFlag+'~')-1)
WHERE AER.AppId > 10000 and AER.RoleId<9999
ORDER BY AER.AppId DESC
GO
GRANT EXEC ON GetAppEle_P to StufilesUser
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

StufilesUser is a DATABASE ROLE - not a username.

You can also write a script to loop through objects and ASSIGN PERMISSIONS to them - that's pretty easy. Do you want to see how to do that??