Post reply

SQL Script to Auto create Security Permissions to a database

  • November 5, 2019 at 9:38 pm

    #3695282

    Hi,

    I want to create a Stored Procedure or script that I can auto-generate a SQL User Account with db_datareader permissions that will allow a User to see into a specific database Views.  I am curious to know if you could provide an example of how to do something like this and/or what tables I would need to use to create a script.

  • November 6, 2019 at 12:51 am

    #3695334

    db_datareader gives permissions to all SELECT-able objects, which would include tables and views.

    I think you wanted just specific views, so you have to turn to dynamic sql:

    offhand, it would look something like this, i think:

    CREATE PROCEDURE MyExample(@LoginName varchar(128))
    AS
    BEGIN
      SET NOCOUNT ON;
      DECLARE @cmd VARCHAR(MAX) =
      '
      GRANT SELECT ON dbo.vw1 TO [TheLoginName];
      GRANT SELECT ON dbo.vw2 TO [TheLoginName];
      GRANT SELECT ON dbo.vw3 TO [TheLoginName];
      GRANT SELECT ON dbo.vw4 TO [TheLoginName];
      GRANT SELECT ON dbo.vw5 TO [TheLoginName];
      '
      SET @cmd = REPLACE(@cmd,'[TheLoginName]',QUOTENAME(@LoginName))
      PRINT @cmd
      EXECUTE(@cmd);
    END -- PROC

    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!

  • November 6, 2019 at 4:39 pm

    #3695482

    Many Thanks!!

  • November 6, 2019 at 4:48 pm

    #3695483

    Better still, from an administrative point of view, would be to create a role and add users to it as and when required.

    John

Viewing 4 posts - 1 through 3 (of 3 total)

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