locked
Specify a single login on specific server role and database RRS feed

  • Question

  • Specify a single login on specific server role and database

    --Server Role

    GRANT
      CONTROL SERVER
     TO
      UserDbaDeveloper

    GRANT CONNECT SQL TO UserDbaDeveloper

    --Sirver para poder realizar Respaldos y Restauraciones de Base de Datos (Acceso a las carpetas del servidor)
     ALTER SERVER ROLE diskadmin
     ADD MEMBER UserDbaDeveloper

    /*Server*/
     --Inicio
     DENY ADMINISTER BULK OPERATIONS TO UserDbaDeveloper
     DENY ALTER ANY AVAILABILITY GROUP TO UserDbaDeveloper
     DENY ALTER ANY CREDENTIAL TO UserDbaDeveloper
     DENY ALTER ANY ENDPOINT TO UserDbaDeveloper
     DENY ALTER ANY EVENT NOTIFICATION TO UserDbaDeveloper
     DENY ALTER ANY EVENT SESSION TO UserDbaDeveloper
     DENY ALTER ANY LINKED SERVER TO UserDbaDeveloper
     DENY ALTER ANY SERVER AUDIT TO UserDbaDeveloper
     DENY ALTER ANY SERVER ROLE TO UserDbaDeveloper
     DENY ALTER RESOURCES TO UserDbaDeveloper
     DENY ALTER SERVER STATE TO UserDbaDeveloper
     DENY ALTER SETTINGS TO UserDbaDeveloper
     DENY AUTHENTICATE SERVER TO UserDbaDeveloper
     DENY IMPERSONATE ANY LOGIN TO UserDbaDeveloper
     DENY SELECT ALL USER SECURABLES TO UserDbaDeveloper
     DENY SHUTDOWN TO UserDbaDeveloper
     DENY UNSAFE ASSEMBLY TO UserDbaDeveloper
     GRANT VIEW ANY DATABASE TO UserDbaDeveloper
     GRANT ALTER ANY DATABASE TO UserDbaDeveloper


    Role Base de Datos


    CREATE ROLE UserDBADev AUTHORIZATION [dbo]


     GRANT
      SELECT
      , INSERT
      , DELETE
      , UPDATE
      , EXECUTE
      , ALTER
     TO
      UserDBADev

    DENY SELECT, DELETE, UPDATE, ALTER ON OBJECT::dbo.DatabaseLog TO UserDbaDev

    Esto lo quiero asignar aun solo login, pero veo que no se puede, porque me toma el del primer rol que es el de Servidor.


    Mi pregunta es como asignar un Rol Servidor y de Base de Datos a un login en especifico.


    English

    This I want to assign only even login, but I see that you can not, because I take the first role is  Server.


    My question is how to assign a Role Server and Database in a specific  login.

    ALTER SERVER ROLE UserDbaDeveloper
    ADD MEMBER bgarcia
    EXEC sp_addrolemember 'UserDBADev', 'bgarcia'

    So I did, but I take the  role of server and I want to take  the server and database.

    Regards.


    SQL Server, Power Builder

    Tuesday, June 24, 2014 6:49 PM

Answers

  • Really we are not very much clear with your question, can you please explain us more on the same and what is the issue are you facing ?

    Just as per my understanding please find the below...

    Create login sql server level...

    CREATE LOGIN TestDBUser WITH PASSWORD = 'Password'
    Now this login now exists in you instance of SQL server, but has no permissions and is not a user on any database.

     To add that user to a database The owner of a specific database would create a USER for that LOGIN:

     CREATE USER TestDBUser FOR LOGIN MyUser

    EXEC sp_grantdbaccess N'MyUser', N'TestDBUser'  
    GO
    Exec sp_addrolemember N'db_owner', N'TestDBUser'
    GO

    Granting Server Level Permissions :-
    Granting rights is pretty straight forward.  To grant "SHUTDOWN" rights to login "TestDBUser" you would issue the following command:

    GRANT SHUTDOWN TO TestDBUser

    SQL 2014 :

    In SQL Server 2014 adds some new server-level permissions that will help you with just read only case - they were designed with auditing in mind. We could simply add the following two permissions to a server-level login:
     
    CONNECT ANY DATABASE
    SELECT ALL USER SECURABLES

    To listing the permissions :

    ===============

    SELECT 
      
    [srvprin].[name] [server_principal],
      
    [srvprin].[type_desc] [principal_type],
      
    [srvperm].[permission_name],
      
    [srvperm].[state_desc] 
    FROM [sys].[server_permissions] srvperm
      
    INNER JOIN [sys].[server_principals] srvprin
        
    ON [srvperm].[grantee_principal_id] [srvprin].[principal_id]
    WHERE [srvprin].[type] IN ('S''U''G')
    ORDER BY [server_principal][permission_name];


    Reference Links :

    http://msdn.microsoft.com/en-us/library/ms189751.aspx

    http://msdn.microsoft.com/en-us/library/ms186320.aspx

    http://msdn.microsoft.com/en-us/library/aa337562.aspx#TsqlProcedure

    http://msdn.microsoft.com/en-us/library/ee677610.aspx -- Creating server role


    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by Maniaco Tuesday, December 9, 2014 8:17 PM
    Wednesday, June 25, 2014 3:39 AM
  • Several issues. First, try not to mix the old fixed server role system with the newer (2005) granular permissions. They don't work well with each other. Stick with the newer system whenever possible.

    Second, your first action gave the UserDbaDeveloper CONTROL SERVER permission. Making that role a member of the diskadmin fixed server role should not be necessary. They already have those permissions.

    You can't mix the server roles/permissions and the database roles/permissions. The server level permissions can only be granted/denied to logins or server roles. The database permissions can only be granted/denied to users or database roles.

    If you haven't seen the permissions poster, make sure you check out http://go.microsoft.com/fwlink/?LinkId=229142


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by tracycai Monday, June 30, 2014 10:40 AM
    Thursday, June 26, 2014 3:53 PM
  • Olá Manacio

    I see you actually took care of a lot important permissions - actually I am wondering, why you use CONTROL SERVER at all?
    If you do, I would like to point out, that You are missing one important permission to deny: ALTER ANY LOGIN

    That still doesn't make it completely safe from elevation though.
    You can also read more here:


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Marked as answer by tracycai Monday, June 30, 2014 10:40 AM
    Thursday, June 26, 2014 6:05 PM

All replies

  • Really we are not very much clear with your question, can you please explain us more on the same and what is the issue are you facing ?

    Just as per my understanding please find the below...

    Create login sql server level...

    CREATE LOGIN TestDBUser WITH PASSWORD = 'Password'
    Now this login now exists in you instance of SQL server, but has no permissions and is not a user on any database.

     To add that user to a database The owner of a specific database would create a USER for that LOGIN:

     CREATE USER TestDBUser FOR LOGIN MyUser

    EXEC sp_grantdbaccess N'MyUser', N'TestDBUser'  
    GO
    Exec sp_addrolemember N'db_owner', N'TestDBUser'
    GO

    Granting Server Level Permissions :-
    Granting rights is pretty straight forward.  To grant "SHUTDOWN" rights to login "TestDBUser" you would issue the following command:

    GRANT SHUTDOWN TO TestDBUser

    SQL 2014 :

    In SQL Server 2014 adds some new server-level permissions that will help you with just read only case - they were designed with auditing in mind. We could simply add the following two permissions to a server-level login:
     
    CONNECT ANY DATABASE
    SELECT ALL USER SECURABLES

    To listing the permissions :

    ===============

    SELECT 
      
    [srvprin].[name] [server_principal],
      
    [srvprin].[type_desc] [principal_type],
      
    [srvperm].[permission_name],
      
    [srvperm].[state_desc] 
    FROM [sys].[server_permissions] srvperm
      
    INNER JOIN [sys].[server_principals] srvprin
        
    ON [srvperm].[grantee_principal_id] [srvprin].[principal_id]
    WHERE [srvprin].[type] IN ('S''U''G')
    ORDER BY [server_principal][permission_name];


    Reference Links :

    http://msdn.microsoft.com/en-us/library/ms189751.aspx

    http://msdn.microsoft.com/en-us/library/ms186320.aspx

    http://msdn.microsoft.com/en-us/library/aa337562.aspx#TsqlProcedure

    http://msdn.microsoft.com/en-us/library/ee677610.aspx -- Creating server role


    Raju Rasagounder Sr MSSQL DBA

    • Marked as answer by Maniaco Tuesday, December 9, 2014 8:17 PM
    Wednesday, June 25, 2014 3:39 AM
  • Several issues. First, try not to mix the old fixed server role system with the newer (2005) granular permissions. They don't work well with each other. Stick with the newer system whenever possible.

    Second, your first action gave the UserDbaDeveloper CONTROL SERVER permission. Making that role a member of the diskadmin fixed server role should not be necessary. They already have those permissions.

    You can't mix the server roles/permissions and the database roles/permissions. The server level permissions can only be granted/denied to logins or server roles. The database permissions can only be granted/denied to users or database roles.

    If you haven't seen the permissions poster, make sure you check out http://go.microsoft.com/fwlink/?LinkId=229142


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by tracycai Monday, June 30, 2014 10:40 AM
    Thursday, June 26, 2014 3:53 PM
  • Olá Manacio

    I see you actually took care of a lot important permissions - actually I am wondering, why you use CONTROL SERVER at all?
    If you do, I would like to point out, that You are missing one important permission to deny: ALTER ANY LOGIN

    That still doesn't make it completely safe from elevation though.
    You can also read more here:


    Andreas Wolter (Blog | Twitter)
    MCM - Microsoft Certified Master SQL Server 2008
    MCSM - Microsoft Certified Solutions Master Data Platform, SQL Server 2012
    www.andreas-wolter.com | www.SarpedonQualityLab.com

    • Marked as answer by tracycai Monday, June 30, 2014 10:40 AM
    Thursday, June 26, 2014 6:05 PM