Deny Alter and Deny Drop permissions on ONLY Stored Procedures

  • coolchaitu

    SSChampion

    Points: 10261

    Good morning,

    We have a generic sql login "prduser". Applications use this login. We want the login NOT to have ALTER PROCEDURE and DROP PROCEDURE permissions only on the stored procedures(there are thousands of them). Please help on how to solve this.

  • Lowell

    SSC Guru

    Points: 323376

    i'd be interested to know why, of course; is it because the username and password are passed around to developers? are you having trouble with people changing procs? are you using anything with change control for your procs?

    as far as i know, the only way to do this is with a Database Level Trigger that checks DDL actions, so you can rollback a a DDL command that was issued if it meets certain criteria(ie the specific user and the ALTER or DROP commands)

    i posted a full fledged example where someone wanted a user to ONLY have the ability to create and alter views(but not table/proc/function).

    you could use that as a model, if you really, really needed to prevent the activity.

    http://www.sqlservercentral.com/Forums/FindPost1241331.aspx

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • coolchaitu

    SSChampion

    Points: 10261

    Dear Lowell sir,

    The Applications use this login in their connection string and perform DML and other operations. Some of the business logic is in the stored procedures and hence the application code uses the stored procedures. However, the requirement is that the login must NOT alter procedure and must NOT drop procedure.It is allowed to do evrything else.

  • coolchaitu

    SSChampion

    Points: 10261

    The login had db_owner permission to the database. Will the trigger override the db_owner permission previlige?

  • jonas.gunnarsson 52434

    Ten Centuries

    Points: 1134

    Hi!

    I maybe misunderstands your question, but you want your app login (prduser) to only have execute rights to procedures and functions?

    You don’t add rights to a login object, you should use a role, which could be reused.

    You could use a script that’s retrieves the objects you want to set some grant for the role, like:

    declare @SchemaName as nvarchar(20) = 'dbo';

    declare @MyAppLogin as nvarchar(128) = 'prduser';

    declare @MyAppUser as nvarchar(128) = 'MyAppUser';

    declare @MyAppRole as nvarchar(128) = 'MyAppRole';

    declare @StoredProcedures as table (name varchar(180));

    insert into @StoredProcedures select name from sys.sysobjects where type = 'P' and left(name, 3) not in ('sp_') and left(name, 5) not in ('Tool_') order by name asc;

    declare @Functions as table (name varchar(180));

    insert into @Functions select name from sys.sysobjects where type in ('FN', 'TF') and left(name, 3) not in ('fn_') order by name asc;

    declare @TableTypes as table (name varchar(180));

    insert into @TableTypes select name from sys.table_types order by name asc;

    declare @statement nvarchar(180);

    declare @statements as table (name varchar(180));

    if not exists (select * from sys.server_principals where name = @MyAppUser)

    begin

    select @statement = N'CREATE USER [' + @MyAppUser + N'] ' + N'FOR LOGIN [' + @MyAppLogin + N']'

    --print @statement

    execute dbo.sp_executesql @statement;

    end

    if not exists (select * from sys.database_principals where name = @MyAppRole and type = 'A')

    begin

    select @statement = N'CREATE ROLE [' + @MyAppRole + N'] AUTHORIZATION [' + @MyAppUser + N']'

    --print @statement

    execute dbo.sp_executesql @statement;

    end

    execute sp_addrolemember @MyAppRole, @MyAppUser;

    insert into @statements select N'GRANT EXECUTE ON OBJECT::[' + name + N'] TO [' + @MyAppRole + N']' from @StoredProcedures

    insert into @statements select N'GRANT EXECUTE ON TYPE::[' + name + N'] TO [' + @MyAppRole + N']' from @TableTypes

    while ((select count(*) from @statements) > 0)

    begin

    set @statement = (select top 1 name from @statements);

    -- print @statement

    execute dbo.sp_executesql @statement;

    delete from @statements where name = @statement;

    end

  • coolchaitu

    SSChampion

    Points: 10261

    Dear Jonas

    We want our app login(prduser) to have ONLY execute permission to procedures.It must NOT be able to ALTER or DROP stored procedure. It is allowed to do everything else. The login is a member of db_owner role.

  • Lowell

    SSC Guru

    Points: 323376

    coolchaitu (8/24/2015)


    Dear Jonas

    We want our app login(prduser) to have ONLY execute permission to procedures.It must NOT be able to ALTER or DROP stored procedure. It is allowed to do everything else. The login is a member of db_owner role.

    there's your answer then.

    take away db owner, and grant execute, db_datareader and db_datawriter.

    that role should not be creating objects or doing backups anyway, so take awy the overreaching rights.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • coolchaitu

    SSChampion

    Points: 10261

    Dear Lowell,

    Thats the catch. The login cannot be removed from db_owner. They have some reasons for it.

  • Lowell

    SSC Guru

    Points: 323376

    you'll probably need to investigate a bit more ; could their reasons for it be simply institutional inertial(we always needed db_owner just in case)

    if an application is using this login, why would it issue ALTER PROC or DROP PROC commands? if it's due to developer misuse, you can fix that.

    you can do the soft way, and add a login trigger that captures hostname +appname that uses that login, and ithe app name contains SQL server management studio, and the hostname to a developer machine, you can approach them individually and tell them don't connect with the app login.

    if you create a database trigger, which would work, they developer would potentially just be slowed down...they could disable it, do their work, and re-enable your trigger.

    i would consider declaring an emergency security fix, and change the password, and change it in the application as well.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Ed Wagner

    SSC Guru

    Points: 286957

    I'm interested in these reasons the application needs the db_owner database role.

    I'm with Lowell on this one: revoke db_owner and grant execute, db_datareader and db_datawriter.

    The login should have the least permissions it needs to do the work it needs to do. My guess is that creating new database users and granting them permissions is not among them. Also, revoking the permissions of others, dropping database users, disabling triggers and detaching the database should not be among them.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply