Is this SQL requirement possible?

I need a script that enumerates the the databases in a given instance of sql server. Then it needs to output the account with permissions on that server. Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Thanks a lot

1. "enumerates the the databases" -yes, possible.

2. "output the account with permissions" -yes, possible.

3. "search stored procedured for reference to specific file and account names loaded from a csv file" -yes, possible.

Any more detailed help requires more detailed information from you.

|||

What I don t understand is this:

"search stored procedured for reference to specific file and account names loaded from a csv file"?

would u be able to guess what that means and if possible give me an example of it, please?

Thanks a lot.

|||

I don't wish to continue playing 'guessing games' with you.

I have previously attempted to 'guess' what you had in mind when you made the original post. If my response doesn't seem 'close to the mark' for you, I suggest that you refine your question and repost.

|||

R.Tutus wrote:

I need a script that enumerates the the databases in a given instance of sql server. Then it needs to output the account with permissions on that server.

The following query will do this..

Create Table #ServerLoginDetails
(
DataBaseName Varchar(1000)
,LoginName Varchar(1000)
,MemberOf Varchar(1000)
)
Go
Exec sp_MSforeachdb 'Insert Into #ServerLoginDetails
Select ''?'',Users.Name Loginname, Isnull(Groups.Name,''NA'') MemberOf From ?..Sysusers Users
Left Outer Join ?..Sysmembers Members On Members.memberuid = Users.Uid
Left Outer Join ?..Sysusers Groups On Groups.Uid = Members.GroupUid And Groups.IsLogin=0
Where Users.IsLogin=1'

Select * from #ServerLoginDetails
Go
Drop Table #ServerLoginDetails

R.Tutus wrote:

Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Need more information buddy..

|||

That s perfect. Tell me what does the question mark "?" refer to in T-SQL. I used the script without it and i think ot still works. why do we hgave to use it?

Also you quoted the last question:

R.Tutus wrote:

Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Do u have a clue on what does means or how it can be solved.

Thanks a lot, i ll let u know if i have more questions in undersatanding your script.

|||

? will replace the database name when you execute the sp_MSForEachDB.

sp_MSForEachDB is one of the system SP but none of the online document guide you (Undocumented SPs)..

If you remove the ? from the query, you wont get all the database's users information. This SP will automatically enumerate each database and execute the query..

|||Please don't use this system stored procedure. It is not documented nor supported. So we can/will remove it or modify the behavior without notice in any version of SQL Server or service pack. Instead you should write your own SP that uses dynamic SQL to execute the cmd or use sp_executesql itself.|||

Yes I agree this...

So tried to change the logic with following query...

Create Table #ServerLoginDetails
(
DataBaseName Varchar(1000)
,LoginName Varchar(1000)
,MemberOf Varchar(1000)
)
Go
Declare @.DB Table (Query varchar(8000));
Declare @.Query as Varchar(5000);
Declare @.Count as int;

Select @.Query = 'Insert Into #ServerLoginDetails
Select ''?'',Users.Name Loginname, Isnull(Groups.Name,''NA'') MemberOf From ?..Sysusers Users
Left Outer Join ?..Sysmembers Members On Members.memberuid = Users.Uid
Left Outer Join ?..Sysusers Groups On Groups.Uid = Members.GroupUid And Groups.IsLogin=0
Where Users.IsLogin=1';

Insert Into @.DB
Select Replace(@.Query,'?',Name) From Master..Sysdatabases;

Select @.Count = Count(Query) from @.DB
While @.Count <> 0
Begin
Select @.Query=Query From @.DB;
Exec(@.Query);
Delete From @.DB Where Query=@.Query;
Select @.Count = Count(Query) from @.DB;
End

Select * from #ServerLoginDetails
Go
Drop Table #ServerLoginDetails

|||

can u explain a bit Mani pls:

what s the member table used for and how did u join the 2. ( i see the code ) but i need explanantions on what joined columns represent in order to understand.

also cn u explain to me just generally the last script, how different it s from the first. just generally.

Thanks a lot.

|||

How can I change the script above (that looks up the users of all the databases) so that I get the permissions as well.

Thank you.

Is this SQL requirement possible?

I need a script that enumerates the the databases in a given instance of sql server. Then it needs to output the account with permissions on that server. Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Thanks a lot

1. "enumerates the the databases" -yes, possible.

2. "output the account with permissions" -yes, possible.

3. "search stored procedured for reference to specific file and account names loaded from a csv file" -yes, possible.

Any more detailed help requires more detailed information from you.

|||

What I don t understand is this:

"search stored procedured for reference to specific file and account names loaded from a csv file"?

would u be able to guess what that means and if possible give me an example of it, please?

Thanks a lot.

|||

I don't wish to continue playing 'guessing games' with you.

I have previously attempted to 'guess' what you had in mind when you made the original post. If my response doesn't seem 'close to the mark' for you, I suggest that you refine your question and repost.

|||

R.Tutus wrote:

I need a script that enumerates the the databases in a given instance of sql server. Then it needs to output the account with permissions on that server.

The following query will do this..

Create Table #ServerLoginDetails
(
DataBaseName Varchar(1000)
,LoginName Varchar(1000)
,MemberOf Varchar(1000)
)
Go
Exec sp_MSforeachdb 'Insert Into #ServerLoginDetails
Select ''?'',Users.Name Loginname, Isnull(Groups.Name,''NA'') MemberOf From ?..Sysusers Users
Left Outer Join ?..Sysmembers Members On Members.memberuid = Users.Uid
Left Outer Join ?..Sysusers Groups On Groups.Uid = Members.GroupUid And Groups.IsLogin=0
Where Users.IsLogin=1'

Select * from #ServerLoginDetails
Go
Drop Table #ServerLoginDetails

R.Tutus wrote:

Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Need more information buddy..

|||

That s perfect. Tell me what does the question mark "?" refer to in T-SQL. I used the script without it and i think ot still works. why do we hgave to use it?

Also you quoted the last question:

R.Tutus wrote:

Finally it then needs to search stored procedured for reference to specific file and account names loaded from a csv file. The results should be output to a text file or displayed in an exportable window format.

Do u have a clue on what does means or how it can be solved.

Thanks a lot, i ll let u know if i have more questions in undersatanding your script.

|||

? will replace the database name when you execute the sp_MSForEachDB.

sp_MSForEachDB is one of the system SP but none of the online document guide you (Undocumented SPs)..

If you remove the ? from the query, you wont get all the database's users information. This SP will automatically enumerate each database and execute the query..

|||Please don't use this system stored procedure. It is not documented nor supported. So we can/will remove it or modify the behavior without notice in any version of SQL Server or service pack. Instead you should write your own SP that uses dynamic SQL to execute the cmd or use sp_executesql itself.|||

Yes I agree this...

So tried to change the logic with following query...

Create Table #ServerLoginDetails
(
DataBaseName Varchar(1000)
,LoginName Varchar(1000)
,MemberOf Varchar(1000)
)
Go
Declare @.DB Table (Query varchar(8000));
Declare @.Query as Varchar(5000);
Declare @.Count as int;

Select @.Query = 'Insert Into #ServerLoginDetails
Select ''?'',Users.Name Loginname, Isnull(Groups.Name,''NA'') MemberOf From ?..Sysusers Users
Left Outer Join ?..Sysmembers Members On Members.memberuid = Users.Uid
Left Outer Join ?..Sysusers Groups On Groups.Uid = Members.GroupUid And Groups.IsLogin=0
Where Users.IsLogin=1';

Insert Into @.DB
Select Replace(@.Query,'?',Name) From Master..Sysdatabases;

Select @.Count = Count(Query) from @.DB
While @.Count <> 0
Begin
Select @.Query=Query From @.DB;
Exec(@.Query);
Delete From @.DB Where Query=@.Query;
Select @.Count = Count(Query) from @.DB;
End

Select * from #ServerLoginDetails
Go
Drop Table #ServerLoginDetails

|||

can u explain a bit Mani pls:

what s the member table used for and how did u join the 2. ( i see the code ) but i need explanantions on what joined columns represent in order to understand.

also cn u explain to me just generally the last script, how different it s from the first. just generally.

Thanks a lot.

|||

How can I change the script above (that looks up the users of all the databases) so that I get the permissions as well.

Thank you.

is this enough to copy user's permissions to another user's from sp_helpprotect

Hi I use exec helprotect to put a user's perimssion into a temp table called #permissions defined with these columns : (DBname,[Owner] ,[Object],[Grantee] , [Grantor] , [ProtectType] , [Action] , [Col] ) like this:

Now when I get those permissions I use this script inside a cursor to replicate each permission to the user: @.newLoginParam

My question: is there any big business scenario in which my script might fail or miss some permissions for any user?

here is the main part of my script:

declare @.objj as varchar(8000)
declare @.grantee as varchar(8000)
declare @.action as varchar(8000)
declare @.col as varchar(8000)
declare @.sqlGrant as varchar(8000)
declare @.ProtectType as varchar(8000)
declare @.count as int
set @.count=0
declare crsMyTblPermis cursor for
select Object,Grantee,ProtectType , Action, Col from #permissions
open crsMyTblPermis
fetch next from crsMyTblPermis into @.objj, @.grantee, @.ProtectType, @.action,@.col
while @.@.fetch_status=0
begin

if (@.objj!='.' and (@.col = '.' OR @.col = '(All+New)'))
begin
set @.sqlGrant = @.ProtectType +@.action + ' on ' + @.objj + ' to ' + @.newLoginParam
end

if (@.objj!='.' and @.col != '.' and @.col != '(All+New)')
begin
set @.sqlGrant = @.ProtectType +@.action + ' on ' + @.objj + '(' + @.col + ') to ' + @.newLoginParam
end

if (@.objj='.')
begin
set @.sqlGrant = @.ProtectType +@.action + ' to ' + @.newLoginParam
end

exec(@.sqlGrant)
fetch next from........

...etc

P.S: The main reason is that I can t test my script now on the production platform on which the script will be run

Thanks for your advice .

I think you nead a space between @.ProtectType and action.

If you need to grant the permission of an existing user to many new users consider using a database role. You can grant the permissions to the role and then just add the new users to the role.

|||

yeah i will also add the new user to the roles that the old user was part of as well. But i guess the old user might also have permissions on databse objects without necessarily belonging to a specific role?do you agree?

Thanks

Is this a permissions problem

I have a stored procedure that creates a temporary table, populates it,
deletes certain records from it and then selects all the data from it.
In query analyzer I get a resultset, however in my VB6 code it doesn't
return a resultset, the recordset isn't even open after running it.
If I run other stored procedures they work no problem and return a resultset
i cant seem to figure out the issue and I am assuming that its down to
permissions, as the only difference in the sp's are that this one uses temp
tables, although I thought that any #tables created inside of a stored
procedure are there till execution of the sp ends. Do I have to set
something that allows temporary tables to be created
TIAI dont think this is a permission problem. Just check to which SP u are
referring to and in which database does the SP reside.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"steven scaife" wrote:

> I have a stored procedure that creates a temporary table, populates it,
> deletes certain records from it and then selects all the data from it.
> In query analyzer I get a resultset, however in my VB6 code it doesn't
> return a resultset, the recordset isn't even open after running it.
> If I run other stored procedures they work no problem and return a results
et
> i cant seem to figure out the issue and I am assuming that its down to
> permissions, as the only difference in the sp's are that this one uses tem
p
> tables, although I thought that any #tables created inside of a stored
> procedure are there till execution of the sp ends. Do I have to set
> something that allows temporary tables to be created
> TIA|||I'm calling the right sp as I can call some others from the same database, I
can pass paramaters to the other sps and they run fine, the only difference
is one creates and uses a temp table, yet it runs fine under QA.
Its just bugging me as I can't seem to figure out the problem
"Chandra" wrote:
> I dont think this is a permission problem. Just check to which SP u are
> referring to and in which database does the SP reside.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "steven scaife" wrote:
>|||I found the solution needed to have
set nocount on in my sp
"steven scaife" wrote:

> I have a stored procedure that creates a temporary table, populates it,
> deletes certain records from it and then selects all the data from it.
> In query analyzer I get a resultset, however in my VB6 code it doesn't
> return a resultset, the recordset isn't even open after running it.
> If I run other stored procedures they work no problem and return a results
et
> i cant seem to figure out the issue and I am assuming that its down to
> permissions, as the only difference in the sp's are that this one uses tem
p
> tables, although I thought that any #tables created inside of a stored
> procedure are there till execution of the sp ends. Do I have to set
> something that allows temporary tables to be created
> TIA|||SET NOCOUNT ON suppresses DONE_IN_PROC messages and can improve performance
by avoiding extra round trips. With the default SET NOCOUNT OFF,
DONE_IN_PROC messages are returned to ADO apps as empty closed recordsets
and can interfere with data retrieval unless you skip them using the
NextRecordset method.
Hope this helps.
Dan Guzman
SQL Server MVP
"steven scaife" <stevenscaife@.discussions.microsoft.com> wrote in message
news:88F9E00F-DFB3-4E44-AAC6-454042C7DE46@.microsoft.com...
>I found the solution needed to have
> set nocount on in my sp
> "steven scaife" wrote:
>sql

Is this a permissions issue?

Hi All I have created a Stored proc to get the all user rights on a server b
y
database. I am going to use this SP in my reporting services 2005. I
created a user with public rights in all Databases on my server. For some
reason unless I grant this user SA rights, it will not return any rows. whe
n
I give him SA rights all rows return correctly. I am attaching my code can
someone see why SA rights are the only rights that seem to work?
TIA,
Joe
Here is the code!
/*
if exists (select * from sysobjects where id =
object_id('SOX_Audit..Temp_DBO_Audit') )
DROP table SOX_Audit..Temp_DBO_Audit
create table SOX_Audit..Temp_DBO_Audit (DBNAme varchar(50),DbFixedRole
varchar(25),MemberName varchar(50),MembersIS Varbinary(85))
if exists (select * from sysobjects where id =
object_id('SOX_Audit..Temp_Users_Audit') )
DROP table SOX_Audit..Temp_Users_Audit
create table SOX_Audit..Temp_Users_Audit (DbFixedRole varchar(25),MemberName
varchar(50),MembersIS Varbinary(85))
*/
delete Temp_DBO_Audit
delete Temp_Users_Audit
declare @.DBName varchar(50), @.str varchar(100)
Declare crsCall cursor for
select name from master..sysdatabases
open crsCall
fetch Next from CrsCall
into @.DBName
while @.@.fetch_status=0
begin
select @.str = 'use ['+@.DBNAMe+']'+ Char(13) +
'insert into SOX_Audit..Temp_Users_Audit exec sp_helprolemember '
--select @.str
exec (@.str)
insert into SOX_Audit..Temp_DBO_Audit
select @.DBNAme, DbFixedRole, MemberName, MembersIS
from SOX_Audit..Temp_Users_Audit
delete SOX_Audit..Temp_Users_Audit
fetch Next from CrsCall
into @.DBName
end
close crsCAll
deallocate crsCAll
select DBName, dbFixedRole, MemberName from SOX_Audit..Temp_DBO_Auditjaylou (jaylou@.discussions.microsoft.com) writes:
> Hi All I have created a Stored proc to get the all user rights on a
> server by database. I am going to use this SP in my reporting services
> 2005. I created a user with public rights in all Databases on my
> server. For some reason unless I grant this user SA rights, it will not
> return any rows. when I give him SA rights all rows return correctly.
> I am attaching my code can someone see why SA rights are the only rights
> that seem to work?
That is indeed a permissions issue. In SQL 2005, you are not permitted
to see metadata in the same way as you were in previous versions of SQL
Server. Essentially, you need permissions to view metadata. Sometimes
that is implicit. If you have SELECT permissions on a table, you also
have VIEW DEFINITION on the table.
The hour is late, and I don't know on the top of my head which exact
privileges that are needed to view this kind of information. Then
again, to be permitted to the information your procedure retrieves,
you should have fairly hefty privileges.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Is there cascading permission?

Let's say that I have a view that has no explicit permissions defined for
users. Let's also say that I write a stored procedure which uses this view.
Now, if I give explicit permissions to a user to EXEC the stored procedure,
will the procedure execute correctly even if I don't explicitily give
permissions (such as SELECT) for the user to the view?
Michael HocksteinYes, it is.
"michael" <howlinghound@.nospam.nospam> wrote in message
news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
> Let's say that I have a view that has no explicit permissions defined for
> users. Let's also say that I write a stored procedure which uses this
> view.
> Now, if I give explicit permissions to a user to EXEC the stored
> procedure,
> will the procedure execute correctly even if I don't explicitily give
> permissions (such as SELECT) for the user to the view?
>
> --
> Michael Hockstein|||So, as long as a user has permissions to execute a stored procedure, the
stored procedure will execute correctly even if the user does not have
explicit permissions defined for the objects consumed within the stored
procedure?
If the user has permissions to execute a stored procedure but that the user
is explicitly denied permissions to objects consumed by the stored procedure
will the stored procedure still execute correctly?
--
Michael Hockstein
"Uri Dimant" wrote:

> Yes, it is.
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
>
>|||So, if a user has permission to execute a stored procedure which in turn
consumes objects that the same user does not have explicit permissions
defined, the stored procedure will execute without security issues?
And, if a user has permission to execute a stored procedure which in turn
consumes objects that the same user is explicitly denied permisions, will th
e
stored procedure still execute without security issues?
Where can I find documentation on how this security cascades?
Michael Hockstein
"Uri Dimant" wrote:

> Yes, it is.
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:CAB858A0-22F5-410C-842E-A0FE19C84343@.microsoft.com...
>
>|||> So, as long as a user has permissions to execute a stored procedure, the
> stored procedure will execute correctly even if the user does not have
> explicit permissions defined for the objects consumed within the stored
> procedure?
yes, it will , unless you have dynamic sql within a stored procedure , then
you'll have to grant permision on undelaying tables

> If the user has permissions to execute a stored procedure but that the
> user
> is explicitly denied permissions to objects consumed by the stored
> procedure
> will the stored procedure still execute correctly?
Yes , it will
"michael" <howlinghound@.nospam.nospam> wrote in message
news:2DF71CC1-0FAF-4D82-A396-782AC53757C9@.microsoft.com...[vbcol=seagreen]
> So, as long as a user has permissions to execute a stored procedure, the
> stored procedure will execute correctly even if the user does not have
> explicit permissions defined for the objects consumed within the stored
> procedure?
> If the user has permissions to execute a stored procedure but that the
> user
> is explicitly denied permissions to objects consumed by the stored
> procedure
> will the stored procedure still execute correctly?
> --
> Michael Hockstein
>
> "Uri Dimant" wrote:
>|||See:
Security -Giving Permissions through Stored Procedures
http://www.sommarskog.se/grantperm.html
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"michael" <howlinghound@.nospam.nospam> wrote in message
news:F84CE2FB-8709-4B68-BB3C-9F86722B9A8E@.microsoft.com...[vbcol=seagreen]
> So, if a user has permission to execute a stored procedure which in turn
> consumes objects that the same user does not have explicit permissions
> defined, the stored procedure will execute without security issues?
> And, if a user has permission to execute a stored procedure which in turn
> consumes objects that the same user is explicitly denied permisions, will
> the
> stored procedure still execute without security issues?
> Where can I find documentation on how this security cascades?
>
> --
> Michael Hockstein
>
> "Uri Dimant" wrote:
>|||Thanks. I'll look at the reference. BTW, your tag line is one of my favorite
all time sayings.
Michael Hockstein
"Arnie Rowland" wrote:

> See:
> Security -Giving Permissions through Stored Procedures
> http://www.sommarskog.se/grantperm.html
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> "michael" <howlinghound@.nospam.nospam> wrote in message
> news:F84CE2FB-8709-4B68-BB3C-9F86722B9A8E@.microsoft.com...
>
>|||Check out 'ownership chains' in the SQL Server Books Online. The principal
is basically that permissions on indirectly referenced objects are not
needed as long as the objects (or schema on 2005) involved have the same
owner.
Hope this helps.
Dan Guzman
SQL Server MVP
"michael" <howlinghound@.nospam.nospam> wrote in message
news:0EED2BFD-1EA5-4DA8-A512-C4D8E91CEFC0@.microsoft.com...[vbcol=seagreen]
> Thanks. I'll look at the reference. BTW, your tag line is one of my
> favorite
> all time sayings.
> --
> Michael Hockstein
>
> "Arnie Rowland" wrote:
>