generate grants on all databases using sp_msforeachdb - bug?

Question

generate grants on all databases using sp_msforeachdb - bug?

sunshine

SSChasing Mays

Points: 636
More actions
June 12, 2007 at 1:23 pm

#68576

I created a script using sp_msforeachdb that grants all permissions to database users and it works like a champ, except it included privs for roles in the msdb that I don't want. Since the unwanted roles have a uid greater than 16000, I tried exclude them in the where clause. I can use a less than symbol or a greater than symbol with no problem, but when I try to use between or (uid > 4 and uid < 16000), I get no results on any database. Is this a M$ bug?
This logic works from any individual database:
SELECT     *
FROM     sysprotects sysp INNER JOIN
         sysobjects syso ON sysp.id = syso.id INNER JOIN
         sysusers sysu ON sysp.uid = sysu.uid
WHERE (syso.name not like 'sys%' and syso.name not like 'dt%')
AND (sysu.uid > 3 AND sysu.uid < 16000)
Below is the sp_msforeachdb statement that generates the results I want except for the msdb database. If I change the clause AND sysu.uid > 4 to AND (sysu.uid > 3 AND sysu.uid < 16000) I get no results.
-- uncomment "AND" statements of where clause to include only certain user types
sp_msforeachdb 'use [?] SELECT ''USE '' + quotename(''?'')
SELECT
CASE sysp.protecttype
WHEN 205 THEN ''GRANT ''
WHEN 206 THEN ''DENY ''
END +
CASE sysp.action
WHEN 26 THEN ''REFERENCES''
WHEN 193 THEN ''SELECT''
WHEN 195 THEN ''INSERT''
WHEN 196 THEN ''DELETE''
WHEN 197 THEN ''UPDATE''
WHEN 198 THEN ''CREATE TABLE''
WHEN 203 THEN ''CREATE DATABASE''
WHEN 204 THEN ''GRANT_W_GRANT''
WHEN 205 THEN ''GRANT''
WHEN 206 THEN ''REVOKE''
WHEN 207 THEN ''CREATE VIEW''
WHEN 222 THEN ''CREATE PROCEDURE''
WHEN 224 THEN ''EXECUTE''
WHEN 228 THEN ''DUMP DATABASE''
WHEN 233 THEN ''CREATE DEFAULT''
WHEN 235 THEN ''DUMP TRANSACTION''
WHEN 236 THEN ''CREATE RULE''
END
+ '' ON '' + syso.name + '' TO '' + sysu.name +
CASE sysp.protecttype
WHEN 204 THEN ''WITH GRANT OPTION''
ELSE ''''
END
FROM     sysprotects sysp INNER JOIN
         sysobjects syso ON sysp.id = syso.id INNER JOIN
         sysusers sysu ON sysp.uid = sysu.uid
WHERE syso.name not like ''sys%'' and syso.name not like ''dt%''
AND sysu.uid > 4
-- AND sysu.isntgroup = 1
--AND sysu.isntuser = 1
--AND sysu.issqluser = 1
AND sysu.issqlrole = 1
ORDER BY sysu.name,syso.name'
Any ideas why the wierd behavior?

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

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

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

The where clause includes "AND sysu.issqlrole = 1" , which means only to include roles but, by convention, roles will have a uid of greater than 16384, so when both conditions are included, no rows will be returned.

Try without "AND sysu.issqlrole = 1"

SQL = Scarcely Qualifies as a Language

sunshine SSChasing Mays Points: 636 More actions · Answer 2

Thanks Carl,

The problem is I don't want all the Sql authenticated users or windows users, I only want roles in this case. Your answer helped me figure out the real problem though. In msdb the uid 16400 is for the TargetServerRole and 16401 is for the RepositoryUser role. In the other user databases those uids are for a home-grown Sql role. Guess I'm stuck unless I join to something that lets me exclude the msdb database.