SQL Server '05: Adding New Users and setting permissions

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)

Code:

---


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?

Code:

---

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

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:

Code:

---

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:

Code:

---

CREATE PROCEDURE MyProc.up_procname

---

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

Code:

---

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.

Quote:

---

Code:

---

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 :thumb:

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

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

Code:

---

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??