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.