Script User and Role Object Permissions in SQL Server

hi,

checking this out.

http://www.sql-server-performance.com/2002/object-permission-scripts/

when running the script, it said::

Msg 102, Level 15, State 1, Line 30
Incorrect syntax near '”'.
Msg 102, Level 15, State 1, Line 65
Incorrect syntax near '”'.
Msg 102, Level 15, State 1, Line 98
Incorrect syntax near '”'.
Msg 102, Level 15, State 1, Line 114
Incorrect syntax near '–'.
Msg 102, Level 15, State 1, Line 121
Incorrect syntax near '”'.
Msg 102, Level 15, State 1, Line 137
Incorrect syntax near '–'.


what is the problem of it?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Must be a bad format from copy/paste. The double quotes might be really two single quotes. Replace them with two single quotes and try again.
The same for the minus signal.
0
PortletPaulfreelancerCommented:
cannot just copy/paste/run that SQL

is there a code download from that source instead?

best I could do was this, utterly untested
DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName = 'user_name_goes_here'

SET NOCOUNT ON
DECLARE @errStatement [varchar](8000)
      , @msgStatement [varchar](8000)
      , @DatabaseUserID [smallint]
      , @ServerUserName [sysname]
      , @RoleName [varchar](8000)
      , @ObjectID [int]
      , @ObjectName [varchar](261)



SELECT
      @DatabaseUserID = [sysusers].[uid]
    , @ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
      INNER JOIN [master].[dbo].[syslogins] ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
      SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
      'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
      RAISERROR (@errStatement, 16, 1)
END
ELSE
BEGIN
      SET @msgStatement = '–Security creation script for user '
      + @ServerUserName
      + CHAR(13)
      + '–Created At: '
      + CONVERT(varchar, GETDATE(), 112)
      + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '')
      + CHAR(13)
      + '–Created By: '
      + SUSER_NAME()
      + CHAR(13)
      + '–Add User To Database'
      + CHAR(13)
      + 'USE ['
      + DB_NAME()
      + ']'
      + CHAR(13)
      + 'EXEC [sp_grantdbaccess]'
      + CHAR(13)
      + CHAR(9) + '@loginame = '''
      + @ServerUserName
      + ''','
      + CHAR(13)
      + CHAR(9)
      + '@name_in_db = '''
      + @DatabaseUserName + ''''
      + CHAR(13)
      + 'GO'
      + CHAR(13)
      + '–Add User To Roles'
      PRINT @msgStatement
      DECLARE _sysusers CURSOR
      LOCAL
      FORWARD_ONLY
      READ_ONLY FOR
      SELECT
            [name]
      FROM [dbo].[sysusers]
      WHERE [uid] IN (
                  SELECT
                        [groupuid]
                  FROM [dbo].[sysmembers]
                  WHERE [memberuid] = @DatabaseUserID
            )
      OPEN _sysusers
      FETCH
      NEXT
      FROM _sysusers
      INTO @RoleName
      WHILE @@FETCH_STATUS = 0
      BEGIN
            SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
            CHAR(9) + '@rolename = ''' + @RoleName + ''',' + CHAR(13) +
            CHAR(9) + '@membername = ''' + @DatabaseUserName + ''''
            PRINT @msgStatement
            FETCH
            NEXT
            FROM _sysusers
            INTO @RoleName
      END
      SET @msgStatement = 'GO' + CHAR(13) + '–Set Object Specific Permissions'
      PRINT @msgStatement
      DECLARE _sysobjects CURSOR
      LOCAL
      FORWARD_ONLY
      READ_ONLY FOR
      SELECT
      DISTINCT
            ([sysobjects].[id])
          , '[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
      FROM [dbo].[sysprotects]
            INNER JOIN [dbo].[sysobjects] ON [sysprotects].[id] = [sysobjects].[id]
      WHERE [sysprotects].[uid] = @DatabaseUserID
      OPEN _sysobjects
      FETCH
      NEXT
      FROM _sysobjects
      INTO
      @ObjectID,
      @ObjectName
      WHILE @@FETCH_STATUS = 0
      BEGIN
            SET @msgStatement = ''
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 193
                              AND [protecttype] = 205
                  )
                  SET @msgStatement = @msgStatement + 'SELECT,'
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 195
                              AND [protecttype] = 205
                  )
                  SET @msgStatement = @msgStatement + 'INSERT,'
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 197
                              AND [protecttype] = 205
                  )
                  SET @msgStatement = @msgStatement + 'UPDATE,'
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 196
                              AND [protecttype] = 205
                  )
                  SET @msgStatement = @msgStatement + 'DELETE,'
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 224
                              AND [protecttype] = 205
                  )
                  SET @msgStatement = @msgStatement + 'EXECUTE,'
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 26
                              AND [protecttype] = 205
                  )
                  SET @msgStatement = @msgStatement + 'REFERENCES,'
            IF LEN(@msgStatement) > 0
            BEGIN
                  IF RIGHT(@msgStatement, 1) = ','
                        SET @msgStatement = LEFT(@msgStatement, (LEN(@msgStatement) – 1))
                        SET @msgStatement = 'GRANT' + CHAR(13) +
                        CHAR(9) + @msgStatement + CHAR(13) +
                        CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
                        CHAR(9) + 'TO ' + @DatabaseUserName
                  PRINT @msgStatement
            END
            SET @msgStatement = ''
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 193
                              AND [protecttype] = 206
                  )
                  SET @msgStatement = @msgStatement + 'SELECT,'
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 195
                              AND [protecttype] = 206
                  )
                  SET @msgStatement = @msgStatement + 'INSERT,'
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 197
                              AND [protecttype] = 206
                  )
                  SET @msgStatement = @msgStatement + 'UPDATE,'
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 196
                              AND [protecttype] = 206
                  )
                  SET @msgStatement = @msgStatement + 'DELETE,'
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 224
                              AND [protecttype] = 206
                  )
                  SET @msgStatement = @msgStatement + 'EXECUTE,'
            IF EXISTS (
                        SELECT
                              *
                        FROM [dbo].[sysprotects]
                        WHERE [id] = @ObjectID
                              AND [uid] = @DatabaseUserID
                              AND [action] = 26
                              AND [protecttype] = 206
                  )
                  SET @msgStatement = @msgStatement + 'REFERENCES,'
            IF LEN(@msgStatement) > 0
            BEGIN
                  IF RIGHT(@msgStatement, 1) = ','
                        SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) – 1)
                        SET @msgStatement = 'DENY' + CHAR(13) +
                        CHAR(9) + @msgStatement + CHAR(13) +
                        CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
                        CHAR(9) + 'TO ' + @DatabaseUserName
                  PRINT @msgStatement
            END
            FETCH
            NEXT
            FROM _sysobjects
            INTO
            @ObjectID,
            @ObjectName
      END
      CLOSE _sysobjects
      DEALLOCATE _sysobjects
      PRINT 'GO'
END

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
no, there are no code for download.

what is utterly untested ?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
what I did is untested. but I think it is incomplete anyway (I copied page 1 of the original)
0
marrowyungSenior Technical architecture (Data)Author Commented:
it shows this :

Msg 102, Level 15, State 1, Line 174
Incorrect syntax near '–'.
Msg 102, Level 15, State 1, Line 245
Incorrect syntax near '–'.


and I fix it but the result is not correct, it sometimes said it can't find a user but that user already there and for some SQL login it can find, it give shit like this:

–Security creation script for user xxx

–Created At: 20151119235412
–Created By: yyy
–Add User To Database
USE [master]
EXEC [sp_grantdbaccess]
	@loginame = 'xxx',
	@name_in_db = 'xxx'
GO
–Add User To Roles
GO
–Set Object Specific Permissions
GO

Open in new window


what is that mean? no use at all, right?
0
PortletPaulfreelancerCommented:
I truly really absolutely have no idea what that code is/was/should do.

I simply re-formatted it, that is all. It was painful to get as far as I did due to very strange quotation/punctuation mark found throughout the code. Plus I only did page 1 of 2.

If you really need it to work I suggest you break it into smaller parts starting from the beginning. Understand what each part is trying to achieve and then make each part work, once it works add the next piece, get that to work and continue adding pieces until you get the whole thing operational.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"I truly really absolutely have no idea what that code is/was/should do."

ok, sorry about that. let me check in detail and if this is bullshit I will just ignore this link ! rubblish link every where I knew

that's why I hate GOOGLE that as you can see the result is .. . !?
0
marrowyungSenior Technical architecture (Data)Author Commented:
if I copy and try to fix the symbol without modifying anything, I can go up to this point:

DECLARE @DatabaseUserName [sysname]
SET @DatabaseUserName ='bchung'

SET NOCOUNT ON
DECLARE
@errStatement [varchar](8000),
@msgStatement [varchar](8000),
@DatabaseUserID [smallint],
@ServerUserName [sysname],
@RoleName [varchar](8000),
@ObjectID [int],
@ObjectName [varchar](261)

SELECT
@DatabaseUserID = [sysusers].[uid],
@ServerUserName = [master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
WHERE [sysusers].[name] = @DatabaseUserName
IF @DatabaseUserID IS NULL
BEGIN
SET @errStatement = 'User ' + @DatabaseUserName + ' does not exist in ' + DB_NAME() + CHAR(13) +
'Please provide the name of a current user in ' + DB_NAME() + ' you wish to script.'
RAISERROR(@errStatement, 16, 1)
END
ELSE
BEGIN
SET @msgStatement = '–Security creation script for user ' + @ServerUserName + CHAR(13) +
'–Created At: ' + CONVERT(varchar, GETDATE(), 112) + REPLACE(CONVERT(varchar, GETDATE(), 108), ':', '') + CHAR(13) +
'–Created By: ' + SUSER_NAME() + CHAR(13) +
'–Add User To Database' + CHAR(13) +
'USE [' + DB_NAME() + '] ' + CHAR(13) +
'EXEC [sp_grantdbaccess]' + CHAR(13) +
CHAR(9) + '@loginame = "' + @ServerUserName + ''',' + CHAR(13) +
CHAR(9) + '@name_in_db = "' + @DatabaseUserName + '''' + CHAR(13) +
'GO' + CHAR(13) +
'–Add User To Roles'
PRINT @msgStatement
DECLARE _sysusers
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
[name]
FROM [dbo].[sysusers]
WHERE
[uid] IN
(
SELECT
[groupuid]
FROM [dbo].[sysmembers]
WHERE [memberuid] = @DatabaseUserID
)
OPEN _sysusers
FETCH
NEXT
FROM _sysusers
INTO @RoleName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = 'EXEC [sp_addrolemember]' + CHAR(13) +
CHAR(9) + '@rolename = "' + @RoleName + ''',' + CHAR(13) +
CHAR(9) + '@membername = "' + @DatabaseUserName + " " 
PRINT @msgStatement
FETCH
NEXT
FROM _sysusers
INTO @RoleName
END
SET @msgStatement = 'GO' +CHAR(13)+ '- Set Object Specific Permissions'
PRINT @msgStatement
DECLARE _sysobjects
CURSOR
LOCAL
FORWARD_ONLY
READ_ONLY
FOR
SELECT
DISTINCT([sysobjects].[id]),
'[' + USER_NAME([sysobjects].[uid]) + '].[' + [sysobjects].[name] + ']'
FROM [dbo].[sysprotects]
INNER JOIN [dbo].[sysobjects]
ON [sysprotects].[id] = [sysobjects].[id]
WHERE [sysprotects].[uid] = @DatabaseUserID
OPEN _sysobjects
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 205)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'GRANT' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
SET @msgStatement = ''
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 193 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'SELECT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 195 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'INSERT,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 197 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'UPDATE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 196 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'DELETE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 224 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'EXECUTE,'
IF EXISTS(SELECT * FROM [dbo].[sysprotects] WHERE [id] = @ObjectID AND [uid] = @DatabaseUserID AND [action] = 26 AND [protecttype] = 206)
SET @msgStatement = @msgStatement + 'REFERENCES,'
IF LEN(@msgStatement) > 0
BEGIN
IF RIGHT(@msgStatement, 1) = ','
SET @msgStatement = LEFT(@msgStatement, LEN(@msgStatement) - 1)
SET @msgStatement = 'DENY' + CHAR(13) +
CHAR(9) + @msgStatement + CHAR(13) +
CHAR(9) + 'ON ' + @ObjectName + CHAR(13) +
CHAR(9) + 'TO ' + @DatabaseUserName
PRINT @msgStatement
END
FETCH
NEXT
FROM _sysobjects
INTO
@ObjectID,
@ObjectName
END
CLOSE _sysobjects
DEALLOCATE _sysobjects
PRINT 'GO'
END

Open in new window



and parser still show me this:

Msg 207, Level 16, State 1, Line 66
Invalid column name ' '.

can't see why, any idea?

once this still doesn't work, I will give up !
0
marrowyungSenior Technical architecture (Data)Author Commented:
this line fixed:

CHAR(9) + '@membername = ''' + @DatabaseUserName + " "

to this :

CHAR(9) + '@membername = '' ' + @DatabaseUserName + ''''

it still show me the same B.S.

symbol is funny !

however, this script I found out that we have to specify the DB in concern by using "use <database>" first for both script.

then it works much better, user script done much quicker
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I fix it but the result is not correct, it sometimes said it can't find a user but that user already there and for some SQL login it can find
Can you show a real example of this situation?


rubblish link every where I knew
 that's why I hate GOOGLE that as you can see the result is .. . !?
Well, Google didn't write that article, did he? Don't blame the tool but the author :)
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Can you show a real example of this situation?"

I shown above .

"–Security creation script for user xxx

–Created At: 20151119235412
–Created By: yyy
–Add User To Database
USE [master]
EXEC [sp_grantdbaccess]
      @loginame = 'xxx',
      @name_in_db = 'xxx'
GO
–Add User To Roles
GO
–Set Object Specific Permissions
GO

"Well, Google didn't write that article, did he? Don't blame the tool but the author :) "

:):) smart Victor, you create a good joke !

I talking about someone just tell me to google that and I usually can't google a good one !~
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sorry, the example I asked for it was with real login and user database names. I'm trying to check why some are returned and some not.
0
marrowyungSenior Technical architecture (Data)Author Commented:
I have to add user database name and fix the rest ! the role one need a long time to run.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tks, even I finally fix it myself,

you all proactively try to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.