read usergroup for a username with stored procedure

Hi

I wounder if there is a sp in the master db that can give me what usergroup a username is connected to / and or give me info if the username is valid (Windows NT login).

If not: then how do I read what usergroup a username is connected to? sysxlogin table in master db? And can I connect to a sp in master db from another db? Can I create a sp in my db that reads in the master db?

Thanks!!

Best Regards

Staffan

Yes, sp_helpuser

As long as your login has privileges to run system procedures in the master database, you will be able to run the procedure from any database (SQL Server always checks the master database if the procedure starts with sp_ )

sql

Read only and access to specific databases

Hello everyone,
Please forgive me as I'm VERY new to SQL Server. I am (in a former and
current life) a Oracle Certified Master DBA, however, I've been given a
new project -- SQL Server 2005 to administrate. Although I'm going
through some material on SQL Server, I do have two questions that
hopefully someone can answer for me.
I first want to make a READ-ONLY user (a user who can only query the
database) but not do any updates, deletes, etc. I added a local group
to the box (and put the users into that local group) and then added
that group to the "master" security users and then added that group to
the specific database I want them to have access to.
My question is, I don't see where I can make that group readonly to
that specific database, and I also don't see where I can give them
access to ONLY that one database. They can so far query the other
remaining 6 databases on the system by using owner.schema.table syntax,
and I don't want them to be able to do that.
I know this is prob. a pretty simple question and task, but I'm stumped
at how SQL Server does this... although i'm sure the solution is as
simple as the question.
Thanks for all your imput and help!
Create a SQL Login or add the users Windows account (or
Windows group) as a login on the SQL Server box. The login
gives the user access to the server but not to the databases
themselves. It's kind of like a grant connect.
For read only access to a particular database, you can then
add that login as a user in the database and add the user to
the built in database role db_datareader. That would give
the user select on the tables. You could additionally add
the user to db_denydatawriter and that would deny their
ability to insert, update, delete on the tables.
As for why you are seeing some variances, permissions are
cumulative. If the user is a member of other groups that
have access to databases, database objects then the user
gets access that way. If the public database role has been
granted access more than the default access, the user would
pick these up as well as all database users are members of
the public role.
The user having access to the other databases you don't
explicitly add the user to could be due to membership in a
windows group that has been given access - so they inherit
access to the database through their group membership. Or
enabling the guest account in that database. If the guest
account is enabled, anyone with a login can access the
database. By default of how the public role works, the user
would have access to anything granted to public. The guest
account can also be granted additional permissions although
this isn't really a good idea. The guest account is always
exists in master and tempdb and can't be removed from those
two databases.
-Sue
On 14 Mar 2006 08:38:17 -0800, "Greg" <gregcpx@.hotmail.com>
wrote:

>Hello everyone,
>Please forgive me as I'm VERY new to SQL Server. I am (in a former and
>current life) a Oracle Certified Master DBA, however, I've been given a
>new project -- SQL Server 2005 to administrate. Although I'm going
>through some material on SQL Server, I do have two questions that
>hopefully someone can answer for me.
>I first want to make a READ-ONLY user (a user who can only query the
>database) but not do any updates, deletes, etc. I added a local group
>to the box (and put the users into that local group) and then added
>that group to the "master" security users and then added that group to
>the specific database I want them to have access to.
>My question is, I don't see where I can make that group readonly to
>that specific database, and I also don't see where I can give them
>access to ONLY that one database. They can so far query the other
>remaining 6 databases on the system by using owner.schema.table syntax,
>and I don't want them to be able to do that.
>I know this is prob. a pretty simple question and task, but I'm stumped
>at how SQL Server does this... although i'm sure the solution is as
>simple as the question.
>Thanks for all your imput and help!

Read only and access to specific databases

Hello everyone,
Please forgive me as I'm VERY new to SQL Server. I am (in a former and
current life) a Oracle Certified Master DBA, however, I've been given a
new project -- SQL Server 2005 to administrate. Although I'm going
through some material on SQL Server, I do have two questions that
hopefully someone can answer for me.
I first want to make a READ-ONLY user (a user who can only query the
database) but not do any updates, deletes, etc. I added a local group
to the box (and put the users into that local group) and then added
that group to the "master" security users and then added that group to
the specific database I want them to have access to.
My question is, I don't see where I can make that group readonly to
that specific database, and I also don't see where I can give them
access to ONLY that one database. They can so far query the other
remaining 6 databases on the system by using owner.schema.table syntax,
and I don't want them to be able to do that.
I know this is prob. a pretty simple question and task, but I'm stumped
at how SQL Server does this... although i'm sure the solution is as
simple as the question.
Thanks for all your imput and help!Create a SQL Login or add the users Windows account (or
Windows group) as a login on the SQL Server box. The login
gives the user access to the server but not to the databases
themselves. It's kind of like a grant connect.
For read only access to a particular database, you can then
add that login as a user in the database and add the user to
the built in database role db_datareader. That would give
the user select on the tables. You could additionally add
the user to db_denydatawriter and that would deny their
ability to insert, update, delete on the tables.
As for why you are seeing some variances, permissions are
cumulative. If the user is a member of other groups that
have access to databases, database objects then the user
gets access that way. If the public database role has been
granted access more than the default access, the user would
pick these up as well as all database users are members of
the public role.
The user having access to the other databases you don't
explicitly add the user to could be due to membership in a
windows group that has been given access - so they inherit
access to the database through their group membership. Or
enabling the guest account in that database. If the guest
account is enabled, anyone with a login can access the
database. By default of how the public role works, the user
would have access to anything granted to public. The guest
account can also be granted additional permissions although
this isn't really a good idea. The guest account is always
exists in master and tempdb and can't be removed from those
two databases.
-Sue
On 14 Mar 2006 08:38:17 -0800, "Greg" <gregcpx@.hotmail.com>
wrote:
>Hello everyone,
>Please forgive me as I'm VERY new to SQL Server. I am (in a former and
>current life) a Oracle Certified Master DBA, however, I've been given a
>new project -- SQL Server 2005 to administrate. Although I'm going
>through some material on SQL Server, I do have two questions that
>hopefully someone can answer for me.
>I first want to make a READ-ONLY user (a user who can only query the
>database) but not do any updates, deletes, etc. I added a local group
>to the box (and put the users into that local group) and then added
>that group to the "master" security users and then added that group to
>the specific database I want them to have access to.
>My question is, I don't see where I can make that group readonly to
>that specific database, and I also don't see where I can give them
>access to ONLY that one database. They can so far query the other
>remaining 6 databases on the system by using owner.schema.table syntax,
>and I don't want them to be able to do that.
>I know this is prob. a pretty simple question and task, but I'm stumped
>at how SQL Server does this... although i'm sure the solution is as
>simple as the question.
>Thanks for all your imput and help!

Read only and access to specific databases

Hello everyone,
Please forgive me as I'm VERY new to SQL Server. I am (in a former and
current life) a Oracle Certified Master DBA, however, I've been given a
new project -- SQL Server 2005 to administrate. Although I'm going
through some material on SQL Server, I do have two questions that
hopefully someone can answer for me.
I first want to make a READ-ONLY user (a user who can only query the
database) but not do any updates, deletes, etc. I added a local group
to the box (and put the users into that local group) and then added
that group to the "master" security users and then added that group to
the specific database I want them to have access to.
My question is, I don't see where I can make that group readonly to
that specific database, and I also don't see where I can give them
access to ONLY that one database. They can so far query the other
remaining 6 databases on the system by using owner.schema.table syntax,
and I don't want them to be able to do that.
I know this is prob. a pretty simple question and task, but I'm stumped
at how SQL Server does this... although i'm sure the solution is as
simple as the question.
Thanks for all your imput and help!Create a SQL Login or add the users Windows account (or
Windows group) as a login on the SQL Server box. The login
gives the user access to the server but not to the databases
themselves. It's kind of like a grant connect.
For read only access to a particular database, you can then
add that login as a user in the database and add the user to
the built in database role db_datareader. That would give
the user select on the tables. You could additionally add
the user to db_denydatawriter and that would deny their
ability to insert, update, delete on the tables.
As for why you are seeing some variances, permissions are
cumulative. If the user is a member of other groups that
have access to databases, database objects then the user
gets access that way. If the public database role has been
granted access more than the default access, the user would
pick these up as well as all database users are members of
the public role.
The user having access to the other databases you don't
explicitly add the user to could be due to membership in a
windows group that has been given access - so they inherit
access to the database through their group membership. Or
enabling the guest account in that database. If the guest
account is enabled, anyone with a login can access the
database. By default of how the public role works, the user
would have access to anything granted to public. The guest
account can also be granted additional permissions although
this isn't really a good idea. The guest account is always
exists in master and tempdb and can't be removed from those
two databases.
-Sue
On 14 Mar 2006 08:38:17 -0800, "Greg" <gregcpx@.hotmail.com>
wrote:

>Hello everyone,
>Please forgive me as I'm VERY new to SQL Server. I am (in a former and
>current life) a Oracle Certified Master DBA, however, I've been given a
>new project -- SQL Server 2005 to administrate. Although I'm going
>through some material on SQL Server, I do have two questions that
>hopefully someone can answer for me.
>I first want to make a READ-ONLY user (a user who can only query the
>database) but not do any updates, deletes, etc. I added a local group
>to the box (and put the users into that local group) and then added
>that group to the "master" security users and then added that group to
>the specific database I want them to have access to.
>My question is, I don't see where I can make that group readonly to
>that specific database, and I also don't see where I can give them
>access to ONLY that one database. They can so far query the other
>remaining 6 databases on the system by using owner.schema.table syntax,
>and I don't want them to be able to do that.
>I know this is prob. a pretty simple question and task, but I'm stumped
>at how SQL Server does this... although i'm sure the solution is as
>simple as the question.
>Thanks for all your imput and help!

Re : Enabling / Disabling Data Flow Tasks

Hello,

I have created around 10 seperate packages for our application data load. Now I am planning to create a master package (or a wrapper package) which will execute all the 10 packages (thru execute package task). Then I have a job which executes the master package at a given date and time.

Question : How can I enable / disable execution of each package within the master package depending upon a flag variable. The reason why I need this mechanism is if the flag = 0 then I don't want all 10 packages within master package to execute and if flag = 1 then master package execution should begin and subsequently execute all packages within that master package.

Thank you

Jatin Shah

I am sure you could find a way to do this using the enable/disable method, however you may find it easier to use precedence constraints. Create a dummy (null-op) task in the parent package that is the first object to run, and connect it to all 10 sub-package tasks using precedence contraints. Double-click each precedence constraint, set the option to 'success and expression' and set the expression to "@.var == 1" (or 0 as the case may be)

Pretty much the same as what you are proposing, except the precedence constraint is being 'disabled' through a supported mechanism, instead of the tasks itself.

|||

Hello Grant,

Thank You for suggesting a way out.

I tried doing what you have explained, but when I try to set the expression as below

"@.flg == 1" it raises an error.

Thank You

Jatin Shah

|||

What's the error message?

You should check this out: http://www.sqlis.com/default.aspx?306

-Jamie

RDA problem

I am attempting to execute an RDA.pull from my master database and it is generating exceptions the second time I attempt the pull.

NOTE: I always drop the table prior to executing the pull.

It seems that after the first time I execute a pull against a particular table name on the PDA I get the following:

"A duplicate value cannot be inserted into a unique index. [ Table name = __sysRDASubscriptions,Constraint name = c_LocalTableName ]"

Given that the table doesn't exist when I do the pull (I have already dropped it, and verified it's non-existence via the Query Analyzer), why does this error appear? It's almost as though some artifact of the table still exists in the background somewhere and it doesn't like responding to a pull...

Anyone see this before?

This only seems to manifest when I choose one of the trackingOn options--when tracking is off, this doesn't happen.|||

If you call CompactDatabase after dropping the table, does that resolve the error ? This is just a troubleshooting step to try.

Thank you!

Syed N. Yousuf

Microsoft Developer Support Professional

This posting is provided “AS IS” with no warranties, and confers no rights.

|||

Hmm..Drop all these

1) The table you have pulled

2) The error table (if any you chose)

3) The PK Indexes of the pulled table (if you pulled with IndexesOn)

4) The Constraints for that table

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Good idea Syed, but no luck. It didn't seem to make any difference if I compacted the database and then pulled into the new db created.

-Kevin

|||

Thanks for your suggestions Laxmi

I'm having trouble with this. How can I drop the constraints and PK Indexes if I don't know what they're called? It would seem that SQL Server Mobile has assigned generated names to these, and I can't figure out how to drop them.

Is this even possible?

-Kevin

|||

To know the indexes and their information such as on what table and what columns they are created use this query:

SELECT * FROM INFORMATION_SCHEMA.INDEXES;

Similarly for constraints,

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;

SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

Hope this helps!

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation

|||

Hi Laxmi

Thanks for the info. This allows me to see the constraints and indexes, but Sql Mobile doesn't seem to keen on giving them up. When I try to delete my PK constraint (using ALTER TABLE xxx DROP CONSTRAINT) I get a message saying "DDL Operations on this table are restricted".

One thing I found that works is to simply delete the database file and recreate it prior to calling rda.pull. This would seem to be the simplest--are there any problems you see in this approach?

-Kevin

|||

No problem, only if there is no other table in that database. Please make sure that you transfer the data from old database to new database in this approach.

Thanks,

Laxmi Narsimha Rao ORUGANTI, MSFT, SQL Mobile, Microsoft Corporation