Post reply

sp_MSforeachtable

  • January 24, 2008 at 8:00 am

    #71067

    I'm trying to update security permissions on a database and am using the following command;

    sp_MSforeachtable 'GRANT Select ON ? To UserName'

    It parses OK, but does nothing to the table permissions. I've tried various conbinations but can't get any of them to run successfully. Can anyone help me out here?

    Thanks

    Nigel

  • January 24, 2008 at 8:56 am

    #771323

    Run the following command on the current database before yours.

    sp_grantdbaccess @loginame = 'UserName'

    Hopefully, it helps.

  • January 24, 2008 at 9:08 am

    #771333

    Running sp_grantdbaccess @loginame = 'Username' tells that UserName does not exist. If I qualify it with a domain then it tells me that UserName already exists - that is expected as the user already has access to the db.

    However, when I add the domain to the sp call I get a syntax error on "\"

    sp_MSforeachtable 'GRANT Select ON ? To Domain\UserName'

  • January 24, 2008 at 9:17 am

    #771351

    Nigel

    According to Books Online:

    The names of users that are mapped to SQL Server logins, certificates, or asymmetric keys cannot contain the backslash character (\).

    What is the actual name of the user within the database? Please post the results of SELECT name FROM sysusers if you're not sure.

    John

  • January 24, 2008 at 9:21 am

    #771356

    Did you try to use [] to include the UserName?

  • January 24, 2008 at 9:34 am

    #771370

    Tried enclosing [Domain\UserName] but that returns a syntax error "There is no such user or group 'Domain\UserName'".

    Username is returned from sysusers exactly as expected "NPQuiggin"

  • January 24, 2008 at 9:38 am

    #771375

    It is the synchronization issue in MS SQL Server. I tested successfully. Refreshing the current database helped me solve this error.

  • January 25, 2008 at 1:22 am

    #771607

    What happens if you try to assign permissions to individual tables? Choose a table and execute this:

    GRANT SELECT ON dbo.MyTable to NPQuiggin

    John

  • January 25, 2008 at 1:43 am

    #771613

    Refreshing the individual database or all databases has no effect. The permissions have still not been applied.

  • January 25, 2008 at 2:05 am

    #771618

    Gentlemen, Thanks for your help. Problem solved.

    The solution was actually staring me in the face. Open Enterprise Manager, select the CORRECT server...:sick:

    Sincerest appologies

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

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